对于11g中使用了延迟段创建方式创建的表，如果导出时刻表的段没有被创建，那么EXP不会导出这张表。
检查测试如下：
SQL> CREATE TABLE t_defer (id NUMBER, name varchar2(30)); TABLE created. SQL> CREATE TABLE t_imme (id NUMBER, name varchar2(30)) segment creation immediate; TABLE created. SQL> SELECT TABLE_NAME, segment_created FROM user_tables; TABLE_NAME SEG ------------------------------ --- T_IMME YES T_DEFER NO |
下面通过EXP进行导出：
[oracle@DB11G ~]$ EXP yangtk/yangtk file=seg_defer.dmp log=seg_defer.log buffer=2048000 Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:11:39 2011 Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.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 users ... . exporting pre-schema PROCEDURAL objects AND actions . exporting FOREIGN FUNCTION library names FOR USER YANGTK . exporting PUBLIC TYPE synonyms . exporting private TYPE synonyms . exporting object TYPE definitions FOR USER YANGTK About TO export YANGTK's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export YANGTK's TABLES via Conventional Path ... . . exporting TABLE T_IMME 0 ROWS exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional AND extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups AND children . exporting dimensions . exporting post-schema PROCEDURAL objects AND actions . exporting statistics Export TERMINATED successfully WITHOUT warnings. [oracle@DB11G ~]$ EXP yangtk/yangtk file=defer.dmp log=defer.log TABLES=t_defer buffer=2048000 Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:12:42 2011 Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.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 Conventional Path ... EXP-00011: YANGTK.T_DEFER does NOT exist Export TERMINATED successfully WITH warnings. [oracle@DB11G ~]$ EXP yangtk/yangtk file=defer.dmp log=defer.log TABLES=t_defer buffer=2048000 direct=y recordlength=65534 Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:13:04 2011 Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.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 ... EXP-00011: YANGTK.T_DEFER does NOT exist Export TERMINATED successfully WITH warnings. [oracle@DB11G ~]$ expdp yangtk/yangtk dumpfile=seg_defer.dp logfile=seg_defer_expdp.log directory=d_output Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:13:36 2011 Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options Starting "YANGTK"."SYS_EXPORT_SCHEMA_01": yangtk/******** dumpfile=seg_defer.dp logfile=seg_defer_expdp.log directory=d_output Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "YANGTK"."T_DEFER" 0 KB 0 rows . . exported "YANGTK"."T_IMME" 0 KB 0 rows Master table "YANGTK"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for YANGTK.SYS_EXPORT_SCHEMA_01 is: /home/oracle/dmp/seg_defer.dp Job "YANGTK"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:14:33 |
可以看到，无论是使用常规路径还是直接路径，即使直接指定表名，也无法导出延迟段创建的表，而数据泵导出则没有这个问题。
对于已经设置了延迟段创建，又希望EXP导出的时候可以导出所有表，可以通过下面的简单方法：
SQL> BEGIN 2 FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE segment_created = 'NO') loop 3 EXECUTE immediate 'alter table ' || i.table_name || ' allocate extent'; 4 END loop; 5 END; 6 / PL/SQL PROCEDURE successfully completed. SQL> SELECT TABLE_NAME, segment_created FROM user_tables; TABLE_NAME SEG ------------------------------ --- T_IMME YES T_DEFER YES |
这时EXP就可以正确导出所有表了：
[oracle@DB11G ~]$ EXP yangtk/yangtk file=seg_defer_2.dmp log=seg_defer_2.log buffer=2048000 Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 15:44:21 2011 Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.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 users ... . exporting pre-schema PROCEDURAL objects AND actions . exporting FOREIGN FUNCTION library names FOR USER YANGTK . exporting PUBLIC TYPE synonyms . exporting private TYPE synonyms . exporting object TYPE definitions FOR USER YANGTK About TO export YANGTK's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export YANGTK's TABLES via Conventional Path ... . . exporting TABLE T_DEFER 0 ROWS exported . . exporting TABLE T_IMME 0 ROWS exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional AND extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups AND children . exporting dimensions . exporting post-schema PROCEDURAL objects AND actions . exporting statistics Export TERMINATED successfully WITHOUT warnings. |