ALERT: Direct Path Export (EXP) Corrupts The Dump If An Empty Table Partition...

刘泽宇

描述
使用传统的导出实用程序（EXP）执行了直接路径导出（表、模式或全库导出）。如果导出的对象包含空表分区，则会导致导出转储损坏，无法导入。

发生场景
只有exp版本>=11.2.0.1受到影响。传统的导出程序从11g版本开始就不受支持，不再进行维护。

现象
During import you may see one of the following issues:

IMP-00009: abnormal end of export file
Or:

IMP-00051: Direct path exported dump file contains illegal column length
IMP-00008: unrecognized statement in the export file
Or:

Import silently skips a part of the dump, tables are missing and later constraints cannot be created.

This is below demonstrated with a simple test:

connect test
create table part001
(
   col001  number,
   col002  varchar2(100)
)
partition by range (col001)
(
   partition p001 values less than (10),
   partition p002 values less than (100),
   partition p003 values less than (1000)
);

insert into part001 values (5, 'Text 5');
insert into part001 values (500, 'Text 500');
commit;

#> exp test/password file=part001.dmp tables=part001 direct=y

This will show:

About to export specified tables via Direct Path ...
. . exporting table                        PART001
. . exporting partition                           P001          1 rows exported
. . exporting partition                           P002          0 rows exported
. . exporting partition                           P003          1 rows exported
Export terminated successfully without warnings.
but the import breaks with error:

. importing TEST's objects into TEST
. . importing partition               "PART001":"P001"          1 rows imported
. . importing partition               "PART001":"P002"
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.

可采用的方法
If you perform direct path exports using a version greater or equal 11.2.0.1 and you see the messages:

About to export specified tables via Direct Path ...
...
. . exporting partition                     <partition_name>           0 rows exported
...
in the export output (or log file), then you obtain a corrupt dump. You can verify the dump with the commands:

#> imp user/passw full=y

or:

#> imp user/passw full=y show=y

which will show you one of the behaviors listed above.

To workaround this please use:

- conventional path export (exp direct=n)

Or:

- materialize the empty partitions before running direct path exports:

connect / as sysdba
exec dbms_space_admin.materialize_deferred_segments (schema_name => 'TEST', table_name => 'PART001', partition_name => 'P002');
Or:

- DataPump export (expdp)