联系：手机/微信(+86 17813235971) QQ(107644445)
标题：增加默认值列exp DIRECT=Y导出，导入遭遇ORA-01400
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近的一次数据迁移中使用exp导出遭遇到ORA-01400: cannot insert NULL into 错误,凭着经验对其进行了重现,确定是由于增加默认值的列,使用DIRECT=Y 导出导致该问题
创建测试表
创建一张表,并且增加带默认值而且不为空的列，然后使用exp DIRECT=Y 导出数据
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL> alter table t_xifenfei add (c_xifenfei varchar2(30) default 'www.xifenfei.com' not null);
Table altered.
SQL> select c_xifenfei from t_xifenfei where rownum<10;
C_XIFENFEI
------------------------------
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
9 rows selected.
[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000 COMPRESS=NO \
> BUFFER=102400000 STATISTICS=none DIRECT=Y recordlength=65535 file=/tmp/t_xifenfei.dmp
Export: Release 11.2.0.4.0 - Production on Tue Aug 21 15:56:52 2018
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 ZHS16GBK character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table T_XIFENFEI
........
86421 rows exported
Export terminated successfully without warnings.
测试导入数据
导入到另外一个用户下面
[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei.dmp fromuser=xff touser=xff1
Import: Release 11.2.0.4.0 - Production on Tue Aug 21 15:57:53 2018
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 file created by EXPORT:V11.02.00 via direct path
Warning: the objects were exported by XFF, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing XFF's objects into XFF1
. . importing table "T_XIFENFEI"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")
Column : SYS
Column : ICOL$
Column :
Column : 20
Column : 2
Column : TABLE
Column : 24-AUG-2013:11:37:35
Column : 24-AUG-2013:11:47:37
Column : 2013-08-24:11:37:35
Column : VALID
Column : N
Column : N
Column : N
Column : 1
Column :
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")
这里可以看到，故障已经重现ORA-01400: cannot insert NULL into,无法将空值插入到刚刚新加的默认值的列中
使用常规导出
[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000
COMPRESS=NO BUFFER=102400000 STATISTICS=none file=/tmp/t_xifenfei1.dmp
Export: Release 11.2.0.4.0 - Production on Tue Aug 21 16:00:50 2018
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 ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T_XIFENFEI
........
86421 rows exported
Export terminated successfully without warnings.
导入数据
[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei1.dmp fromuser=xff touser=xff1 Import: Release 11.2.0.4.0 - Production on Tue Aug 21 16:04:47 2018 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 file created by EXPORT:V11.02.00 via conventional path Warning: the objects were exported by XFF, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing XFF's objects into XFF1 . . importing table "T_XIFENFEI" 86421 rows imported Import terminated successfully without warnings. [oracle@bogon ~]$ [oracle@bogon ~]$ sqlplus xff1/oracle SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 21 16:10:16 2018 Copyright (c) 1982, 2013, Oracle. 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 SQL> select c_xifenfei from t_xifenfei where rownum<10; C_XIFENFEI ------------------------------ www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com 9 rows selected.
通过测试证明,对于11g新特性增加默认值,而且非为空的列,如果使用exp常规方法导入,再导出没有任何问题,如果使用DIRECT=Y特性,将导致导入失败(ORA-01400).这是由于11g的快速增加列和默认值且不为空的新特性导致的(直接修改字典,并没有真正的去修改数据底层存储).
查询mos发现匹配bug: Bug 13961541 : EXP WITH DIRECT=Y CAN NOT HANDLE ADD COLUMN DEFAULT NOT NULL FEATURE IN 11G