oracle定义非空影响,[转自Oracle官方技术博客]对于一个非空字段定义的表导出后，再imp时候报错ORA-01400: cannot insert NULL into xxx 为何呢?...

最近有客户在11203环境上迁移一个大表的时候发现无法导入到新库，原因是imp时候报大量的ORA-01400: cannot insert NULL into xxx

但是通过查询这个表在原库上却没有null 数据，从表的定义上看也是not null的，而且有default值，这个是为什么呢？

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

==新建表并且插入几条记录

create table maob_t ( a number);

insert into maob_t values(1);

insert into maob_t values(2);

insert into maob_t values(3);

commit;

==对表新增字段并为非空+default 值

sqlplus>alter table maob_t add ( c number default 10 not null);

==第一次导出

exp maob/cdscds tables=maob_t file=maob_t.dmp

About to export specified tables via Conventional Path ...

. . exporting table MAOB_T 3 rows exported

Export terminated successfully without warnings.

导出表之后drop表

sqlplus>drop table maob_t purge;

重新导入

imp maob/cdscds full=y ignore=Y file=maob_t.dmp

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing MAOB's objects into MAOB

. importing MAOB's objects into MAOB

. . importing table "MAOB_T" 3 rows imported

Import terminated successfully without warnings.

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

我们采用exp maob/cdscds file=a_tab.dmp tables=a_tab direct=y 进行第二次导出

Export: Release 11.2.0.4.0 - Production on Thu Sep 14 06:06:26 2017

Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning,OLAP,Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path ...

. . exporting table A_TAB 2 rows exported

Export terminated successfully without warnings.

再次对表进行清理：

-bash-4.1$ sqlplus maob/cdscds

sql> drop table a_tab purge;

Table dropped.

在进行一次导入：

-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

Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning,Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing MAOB's objects into MAOB

. importing MAOB's objects into MAOB

. . 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 ("MAOB"."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 ("MAOB"."A_TAB"."COL001")

Column : 2

Column : 0 rows imported

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，这套新工具也能避免 这个问题。