简介：通过exp DIRECT=y方式导出有分区表的中含有空分区表的，导出文件是不能用的，无法正确导入。

适用版本： Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

测试：

点击( 此处)折叠或打开

SQL > conn firsouler/firsouler
Connected .
SQL > select * from tab ;
no rows selected
SQL > create table part001
2 (
3 col001 number ,
4 col002 varchar2 (100 )
5 )
6 partition by range (col001 )
7 (
8 partition p001 values less than (10 ) ,
9 partition p002 values less than (100 ) ,
10 partition p003 values less than (1000 )
11 ) ;
Table created .
SQL > insert into part001 values (5 , 'Text 5' ) ;
insert into part001 values (500 , 'Text 500' ) ;
commit ;
1 row created .
SQL >
1 row created .
SQL >
Commit complete .
SQL >

导出：

点击( 此处)折叠或打开

[oracle@oemtest ~ ] $ exp firsouler/firsouler file =part001 .dmp tables =part001 direct =y
Export : Release 11 .2 .0 .4 .0 - Production on Wed Nov 9 14 :16 :16 2016
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 PART001
. . exporting partition P001 1 rows exported
. . exporting partition P002 0 rows exported
. . exporting partition P003 1 rows exported
Export terminated successfully without warnings .

导入报错：

点击( 此处)折叠或打开

[oracle@oemtest ~ ] $ imp firsouler/firsouler file =part001 .dmp full =y
Import : Release 11 .2 .0 .4 .0 - Production on Wed Nov 9 14 :18 :42 2016
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 FIRSOULER 's objects into FIRSOULER
. importing FIRSOULER's objects into FIRSOULER
. . importing partition "PART001" : "P001" 1 rows imported
. . importing partition "PART001" : "P002"
IMP -00009 : abnormal end of export file
Import terminated successfully with warnings .
[oracle@oemtest ~ ] $

解决方案：

1、查看导出文件

点击( 此处)折叠或打开

[oracle@oemtest ~ ] $ imp firsouler/firsouler file =part001 .dmp full =y show =y
Import : Release 11 .2 .0 .4 .0 - Production on Wed Nov 9 14 :20 :29 2016
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 FIRSOULER 's objects into FIRSOULER
. importing FIRSOULER's objects into FIRSOULER
"CREATE TABLE "PART001 " ("COL001 " NUMBER, "COL002 " VARCHAR2(100)) PCTFREE 1"
"0 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS " PARTITION BY RANGE"
" ("COL001 " ) (PARTITION "P001 " VALUES LESS THAN (10) PCTFREE 10 PCTUSED 4"
"0 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1"
" FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS " LOGG"
"ING NOCOMPRESS, PARTITION "P002 " VALUES LESS THAN (100) PCTFREE 10 PCTUSED"
" 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS " LOGGING NOCOMPRESS, PARTITIO"
"N "P003 " VALUES LESS THAN (1000) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS"
" 255 STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST"
" GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS " LOGGING NOCOMPRESS )"
. . skipping partition "PART001" : "P001"
. . skipping partition "PART001" : "P002"
IMP -00009 : abnormal end of export file
Import terminated successfully with warnings .
[oracle@oemtest ~ ] $

2、在使用直接导出之前，充实空分区表

点击( 此处)折叠或打开

exec dbms_space_admin .materialize_deferred_segments (schema_name = > 'FIRSOULER' , table_name = > 'PART001' , partition_name = > 'P002' )

3、使用数据泵方式导出(expdp)

参考文件：
- IMPORT FAIL WITH IMP-00051 AND IMP-00008

ITPub博客

【DB】Direct Path EXP Corrupts The Dump If An Empty Table Partition Exists

最新文章