EXP无法导出延迟段创建的表

对于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.
This entry was posted in ORACLE and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *