[20180226]exp 无法dirct的情况.txt
http://blog.csdn.net/leshami/article/details/9146023
传统路径导出 VS 直接路径导出(oracle exp direct=y)
1、两者的差异
a、 Conventional path Export
传统路径模式使用SQL SELECT语句抽取表数据。数据从磁盘读入到buffer cache缓冲区中，行被转移到评估缓冲区。
在此之后根据SQL表达式，将记录返回给导出客户端，然后写入到dump文件。
b、Direct path Export
直接导出模式，数据直接从磁盘中读取到导出session的PGA中，行被直接转移到导出session的私有缓冲区，从而跳过SQL命令处理层。
避免了不必要的数据转换。最后记录返回给导出客户端，写到dump文件。
2、性能问题
a、直接路径导出方式比传统路径方式具有更优的性能，速度更快，因为绕过了SQL命令处理部分。
b、直接路径导出方式支持RECORDLENGTH参数(最大为64k)，该参数值通常建议设置为系统I/O或者DB_BLOCK_SIZE的整数倍
c、影响直接路径导出的具体因素(DB_BLOCK_SIZE，列的类型，I/O性能，即数据文件所在的磁盘驱动器是否单独于dump文件所在的磁盘驱动器)
d、无论是直接路径导出还是传统路径导出产生的dump，在使用imp方式导入时，会耗用相同的时间(注:我认为导入设置更大buffer可以加快导入)
--//看看那些情况无法direct导出.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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);
2.测试1:
--//如果存在alter table t add ( c number default 10 not null);的情况,直接路径导出会出问题.
$ exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
SCOTT@book> alter table t add ( c number default 10 not null);
Table altered.
SCOTT@book> alter table t rename to t1;
Table altered.
$ imp scott/book tables=T file=t.dmp buffer=1048576
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:52:37 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
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.
SCOTT@book> select * from t;
no rows selected
--//如果要使用direct要检查sys.ecol$;表.看看是否这样定义的表.
SCOTT@book> column BINARYDEFVAL format a10
SCOTT@book> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFV
---------- ---------- ----------
90387 2 C10B
SCOTT@book> select object_id,data_object_id,owner,object_name from dba_objects where DATA_OBJECT_ID=90387;
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME
---------- -------------- ------ --------------------
90387 90387 SCOTT T1
--//实际上数据如果不是保存块中的情况,diect都会存在问题.
3.测试2:
SCOTT@book> create table t ( a number,b blob );
Table created.
SCOTT@book> insert into t values(1,'a');
1 row created.
SCOTT@book> insert into t values (2,lpad('a',4000,'a'));
1 row created.
SCOTT@book> commit ;
Commit complete.
$ exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
Export: Release 11.2.0.4.0 - Production on Mon Feb 26 17:07:43 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 ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
Table T will be exported in conventional path.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. . exporting table T 2 rows exported
Export terminated successfully without warnings.
--//注意看下划线包含lob类型,导致变成conventional path.
SCOTT@book> create table empx as select * from emp ;
Table created.
$ exp scott/book file=t.dmp tables=empx,t direct=y RECORDLENGTH=65535 buffer=10485760
Export: Release 11.2.0.4.0 - Production on Mon Feb 26 17:26:29 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 ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table EMPX 14 rows exported
Table T will be exported in conventional path.
. . exporting table T 2 rows exported
Export terminated successfully without warnings.
--//表empx采用direct导出,表t采用conventional导出,这就是为什么也要设置buffer的缘故.这样可以照顾传统路径的导出.
--//继续测试导入的情况.
SCOTT@book> alter table empx rename to empy;
Table altered.
SCOTT@book> alter table t rename to t1;
Table altered.
$ imp scott/book file=t.dmp tables=empx,t direct=y RECORDLENGTH=65535 buffer=10485760
LRM-00101: unknown parameter name 'direct'
IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully
--//导入不支持direct参数,实际上导入不需要direct.
$ imp scott/book file=t.dmp tables=empx,t RECORDLENGTH=65535 buffer=10485760
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 17:43:59 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
~~~~~~~~~~~~~~~~~~~~~~~~ oracle似乎知道导致采用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 "EMPX" 14 rows imported
. . importing table "T" 2 rows imported
Import terminated successfully without warnings.
--//很明显RECORDLENGTH=65535是一个无效参数,导入要加快速度要使用buffer参数.
