[20170918]exp 直接路径导出.txt
--//昨天看链接blogs.oracle.com/database4cn/%e5%af%b9%e4%ba%8e%e4%b8%80%e4%b8%aa%e9%9d%9e%e7%a9%ba%e5%ad%97%e6%ae%b5%e5%ae%9a%e4%b9%89%e7%9a%84%e8%a1%a8%e5%af%bc%e5%87%ba%e5%90%8e%ef%bc%8c%e5%86%8dimp%e6%97%b6%e5%80%99%e6%8a%a5%e9%94%99ora-01400%3a-cannot-insert-null-into-xxx-%e4%b8%ba
--//重复测试:
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t ( a number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
commit;
alter table t add ( c number default 10 not null);
--//注:11g这样并不修改块,而且执行非常快.
2.测试导入导出:
D:\tools\rlwrap>exp scott/btbtms@test01p tables=t file=t.dmp
exp scott/btbtms@test01p tables=t file=t.dmp
Export: Release 12.1.0.1.0 - Production on Mon Sep 18 21:53:54 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 3 rows exported
Export terminated successfully without warnings.
SCOTT@test01p> alter table t rename to t1;
Table altered.
D:\tools\rlwrap>imp scott/btbtms@test01p full=y ignore=Y file=t.dmp
imp scott/btbtms@test01p full=y ignore=Y file=t.dmp
Import: Release 12.1.0.1.0 - Production on Mon Sep 18 21:54:54 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export file created by EXPORT:V12.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T" 3 rows imported
Import terminated successfully without warnings.
3.测试使用直接路径导出:
SCOTT@test01p> drop table t purge;
Table dropped.
SCOTT@test01p> alter table t1 rename to t;
Table altered.
D:\tools\rlwrap>rm t.dmp
D:\tools\rlwrap>exp scott/btbtms@test01p tables=t file=t.dmp direct=y
exp scott/btbtms@test01p tables=t file=t.dmp direct=y
Export: Release 12.1.0.1.0 - Production on Mon Sep 18 21:56:11 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table T 3 rows exported
Export terminated successfully without warnings.
SCOTT@test01p> alter table t rename to t1;
Table altered.
D:\tools\rlwrap>imp scott/btbtms@test01p full=y ignore=Y file=t.dmp
imp scott/btbtms@test01p full=y ignore=Y file=t.dmp
Import: Release 12.1.0.1.0 - Production on Mon Sep 18 21:56:56 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export file created by EXPORT:V12.01.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."C")
Column : 1
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."C")
Column : 2
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."C")
Column : 3
Column : 0 rows imported
Import terminated successfully with warnings.
4.说明:
--//转抄:
从以上的测试来看，当对某一个已经存在数据的表进行了新增了非空+default字段之后，实际上11g因为避免把所有block都修改一遍，所
以并没有真正的update底层数据，而是直接修改了数据字典。这样的好处显而易见，alter 表非常快，不会长时间持有library cache
lock。执行sql查询这个新字段的时候，对于老的数据sql引擎会自动从数据字典里面把default读出来，对于新的数据就直接读取磁盘上
的数据，但是当exp导出的时候，若是采用direct=y，因为跳过sql层，所以直接读取了block，所以老数据的block里面因为没有这个字段
当然最终被处理成null插入新表，所以就出现了上述的问题。那么这个问题解决的办法也很简单，就是采用常规形式导出，避免使用
direct=y，另外oracle 在10g之后就推荐使用expdp+impdp，这套新工具也能避免这个问题。