imp导入数据库报ora-01400错误解决

你的名字 2022-05-09 05:44 783阅读 0赞

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2  
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1   \[Release: 11.1 to 11.2\]  
Information in this document applies to any platform.  
\*\*\*Checked for relevance on 18-NOV-2011\*\*\*

## 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 ##

The root cause of this behavior is the new feature introduced with 11gR1:  
  
\- the columns with property 1073741824 (see the column value PROPERTY in COL$ for column COL001) are columns with defaults that are not saved in the column. This means on disk, the null values for this column must be replaced by its default obtained from the column definition:

`connect / as sysdba select col#, name, property from   col$ where  obj# in (select object_id                 from   dba_objects                 where  owner = 'TEST' and                        object_name = 'A_TAB'); COL#       NAME                           PROPERTY ---------- ------------------------------ ----------          1 ID                                      0          2 COL001                         1073741824 2 rows selected.`

This mechanism doesn't work properly when direct path is used with original export (the value is not replaced by its default but the NULL values is stored in export dump. This will produce ORA-1400 during import).  
  
Original export (exp)is desupported in 11g so there is no opened bug against this product.

## Solution ##

1. Use DataPump export/import utilities (expdp/impdp)  
  
or:  
  
2. Start traditional export utility (exp) in conventional path mode (DIRECT=N).