重庆思庄Oracle、Redhat认证学习论坛

标题: ALERT: Direct Path Export (EXP) Corrupts The Dump If An Empty Table Partition... [打印本页]

作者: 刘泽宇 时间: 2023-11-26 11:33
标题: 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)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)