最近有客户在11203环境上迁移一个大表的时候发现无法导入到新库，原因是imp时候报大量的ORA-01400: cannot insert NULL into xxx@H_502_1@ 但是通过查询这个表在原库上却没有null 数据，从表的定义上看也是not null的，而且有default值，这个是为什么呢？

@H_502_1@ 下面的test case或许给您揭示原因：

==新建表并且插入几条记录@H_502_1@ create table maob_t ( a number);@H_502_1@ insert into maob_t values(1);@H_502_1@ insert into maob_t values(2);@H_502_1@ insert into maob_t values(3);@H_502_1@ commit;

@H_502_1@ ==对表新增字段并为非空+default 值@H_502_1@ sqlplus>alter table maob_t add ( c number default 10 not null);

==第一次导出@H_502_1@ exp maob/cdscds tables=maob_t file=maob_t.dmp@H_502_1@ About to export specified tables via Conventional Path ...@H_502_1@ . . exporting table MAOB_T 3 rows exported@H_502_1@ Export terminated successfully without warnings.

@H_502_1@ 导出表之后drop表@H_502_1@ sqlplus>drop table maob_t purge;

重新导入@H_502_1@ imp maob/cdscds full=y ignore=Y file=maob_t.dmp

Export file created by EXPORT:V11.02.00 via conventional path@H_502_1@ import done in US7ASCII character set and AL16UTF16 NCHAR character set@H_502_1@ import server uses AL32UTF8 character set (possible charset conversion)@H_502_1@ . importing MAOB's objects into MAOB@H_502_1@ . importing MAOB's objects into MAOB@H_502_1@ . . importing table "MAOB_T" 3 rows imported@H_502_1@ Import terminated successfully without warnings.

我们可以看到导出和导入都非常正常

我们采用exp maob/cdscds file=a_tab.dmp tables=a_tab direct=y 进行第二次导出@H_502_1@ Export: Release 11.2.0.4.0 - Production on Thu Sep 14 06:06:26 2017@H_502_1@ Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved.@H_502_1@ Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production@H_502_1@ With the Partitioning,OLAP,Data Mining and Real Application Testing options@H_502_1@ Export done in US7ASCII character set and AL16UTF16 NCHAR character set@H_502_1@ server uses AL32UTF8 character set (possible charset conversion)@H_502_1@ About to export specified tables via Direct Path ...@H_502_1@ . . exporting table A_TAB 2 rows exported@H_502_1@ Export terminated successfully without warnings.

再次对表进行清理：@H_502_1@ -bash-4.1$ sqlplus maob/cdscds @H_502_1@ sql> drop table a_tab purge;@H_502_1@ Table dropped.

在进行一次导入：@H_502_1@ -bash-4.1$ imp maob/cdscds ignore=Y file=a_tab.dmp tables=a_tab

Import: Release 11.2.0.4.0 - Production on Thu Sep 14 06:06:37 2017@H_502_1@ Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. @H_502_1@ Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production@H_502_1@ With the Partitioning,Data Mining and Real Application Testing options@H_502_1@ Export file created by EXPORT:V11.02.00 via direct path@H_502_1@ import done in US7ASCII character set and AL16UTF16 NCHAR character set@H_502_1@ import server uses AL32UTF8 character set (possible charset conversion)@H_502_1@ . importing MAOB's objects into MAOB@H_502_1@ . importing MAOB's objects into MAOB@H_502_1@ . . importing table "A_TAB"@H_502_1@ IMP-00019: row rejected due to ORACLE error 1400@H_502_1@ IMP-00003: ORACLE error 1400 encountered@H_502_1@ ORA-01400: cannot insert NULL into ("MAOB"."A_TAB"."COL001")@H_502_1@ Column : 1@H_502_1@ Column :@H_502_1@ IMP-00019: row rejected due to ORACLE error 1400@H_502_1@ IMP-00003: ORACLE error 1400 encountered@H_502_1@ ORA-01400: cannot insert NULL into ("MAOB"."A_TAB"."COL001")@H_502_1@ Column : 2@H_502_1@ Column : 0 rows imported@H_502_1@ Import terminated successfully with warnings.

问题再现了，从以上的测试来看，当对某一个已经存在数据的表进行了新增了非空+default字段之后，实际上11g因为避免把所有block都修改一遍，所以并没有真正的update底层数据，而是直接修改了数据字典。这样的好处显而易见，alter 表非常快，不会长时间持有library cache lock。执行SQL查询这个新字段的时候，对于老的数据sql引擎会自动从数据字典里面把default读出来，对于新的数据就直接读取磁盘上的数据，但是当exp导出的时候，若是采用direct=y，因为跳过sql层，所以直接读取了block，所以老数据的block里面因为没有这个字段当然最终被处理成null插入新表，所以就出现了上述的问题。那么这个问题 解决的办法也很简单，就是采用常规形式导出，避免使用direct=y，另外oracle 在10g之后就推荐使用expdp+impdp，这套新工具也能避免 这个问题。