ORA-1400 During Import of Export Dump Written in Direct Path Mode (Doc ID 826746.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 02-Oct-2014***

Symptoms

You exported a table using the original export utility (exp) in direct path mode. During import, you received the errors:

. . importing table "A_TAB"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("TEST"."A_TAB"."COL001")
Column : 1
Column :

despite the mentioned column COL001 has a default value.

This can be reproduced with the following test:

connect / as sysdba
drop user test cascade;

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;

connect test/test

create table a_tab
(
   id number not null
);

insert into a_tab (id) values (1);
insert into a_tab (id) values (2);
commit;

alter table a_tab add (col001 number (6,2) default 1 not null);

select * from a_tab;

ID         COL001
---------- ----------
         1          1
         2          1

2 rows selected.

#> exp test/test file=a_tab.dmp tables=a_tab direct=y


Export: Release 11.1.0.7.0 - Production on Mon May 18 17:03:46 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table A_TAB 2 rows exported
Export terminated successfully without warnings.

 

connect test/test

drop table a_tab purge;

#> imp test/test file=a_tab.dmp tables=a_tab


Import: Release 11.1.0.7.0 - Production on Mon May 18 17:19:21 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table "A_TAB"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("TEST"."A_TAB"."COL001")
Column : 1
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("TEST"."A_TAB"."COL001")
Column : 2
Column : 0 rows imported
Import terminated successfully with warnings.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms