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

xysoul_雲龍發表於2016-11-09


簡介：透過exp  DIRECT=y方式匯出有分割槽表的中含有空分割槽表的，匯出檔案是不能用的，無法正確匯入。

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


測試：


點選( 此處)摺疊或開啟

  1. SQL > conn firsouler/firsouler
  2. Connected .
  3. SQL > select * from tab ;

  5. no rows selected

  7. SQL > create table part001
  8.   2 (
  9.   3     col001  number ,
  10.   4     col002  varchar2 (100 )
  11.   5 )
  12.   6  partition by range (col001 )
  13.   7 (
  14.   8     partition p001 values less than (10 ) ,
  15.   9     partition p002 values less than (100 ) ,
  16.  10     partition p003 values less than (1000 )
  17.  11 ) ;

  19. Table created .
  20. SQL > insert into part001 values (5 , 'Text 5' ) ;
  21. insert into part001 values (500 , 'Text 500' ) ;
  22. commit ;

  24. 1 row created .

  26. SQL >
  27. 1 row created .

  29. SQL >
  30. Commit complete .

  32. SQL >



匯出：

點選( 此處)摺疊或開啟

  1. [oracle@oemtest ~ ] $ exp firsouler/firsouler file =part001 .dmp tables =part001 direct =y

  3. Export : Release 11 .2 .0 .4 .0 - Production on Wed Nov 9 14 :16 :16 2016

  5. Copyright (c ) 1982 , 2011 , Oracle and / or its affiliates . All rights reserved .


  8. Connected to : Oracle Database 11g Enterprise Edition Release 11 .2 .0 .4 .0 - 64bit Production
  9. With the Partitioning , OLAP , Data Mining and Real Application Testing options
  10. Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

  12. About to export specified tables via Direct Path . . .
  13. . . exporting table                        PART001
  14. . . exporting partition                           P001          1 rows exported
  15. . . exporting partition                           P002          0 rows exported
  16. . . exporting partition                           P003          1 rows exported
  17. Export terminated successfully without warnings .


匯入報錯：

點選( 此處)摺疊或開啟

  1. [oracle@oemtest ~ ] $ imp firsouler/firsouler file =part001 .dmp full =y

  3. Import : Release 11 .2 .0 .4 .0 - Production on Wed Nov 9 14 :18 :42 2016

  5. Copyright (c ) 1982 , 2011 , Oracle and / or its affiliates . All rights reserved .


  8. Connected to : Oracle Database 11g Enterprise Edition Release 11 .2 .0 .4 .0 - 64bit Production
  9. With the Partitioning , OLAP , Data Mining and Real Application Testing options

  11. Export file created by EXPORT :V11 .02 .00 via direct path
  12. import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
  13. . importing FIRSOULER 's objects into FIRSOULER
  14. . importing FIRSOULER's objects into FIRSOULER
  15. . . importing partition "PART001" : "P001" 1 rows imported
  16. . . importing partition "PART001" : "P002"
  17. IMP -00009 : abnormal end of export file
  18. Import terminated successfully with warnings .
  19. [oracle@oemtest ~ ] $


解決方案：

1、檢視匯出檔案

點選( 此處)摺疊或開啟

  1. [oracle@oemtest ~ ] $ imp firsouler/firsouler file =part001 .dmp full =y show =y

  3. Import : Release 11 .2 .0 .4 .0 - Production on Wed Nov 9 14 :20 :29 2016

  5. Copyright (c ) 1982 , 2011 , Oracle and / or its affiliates . All rights reserved .


  8. Connected to : Oracle Database 11g Enterprise Edition Release 11 .2 .0 .4 .0 - 64bit Production
  9. With the Partitioning , OLAP , Data Mining and Real Application Testing options

  11. Export file created by EXPORT :V11 .02 .00 via direct path
  12. import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
  13. . importing FIRSOULER 's objects into FIRSOULER
  14. . importing FIRSOULER's objects into FIRSOULER
  15.   "CREATE TABLE "PART001 " ("COL001 " NUMBER, "COL002 " VARCHAR2(100))  PCTFREE 1"
  16.   "0 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS "  PARTITION BY RANGE"
  17.   " ("COL001 " )  (PARTITION "P001 " VALUES LESS THAN (10)  PCTFREE 10 PCTUSED 4"
  18.   "0 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1"
  19.   " FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS " LOGG"
  20.   "ING NOCOMPRESS, PARTITION "P002 " VALUES LESS THAN (100)  PCTFREE 10 PCTUSED"
  21.   " 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS " LOGGING NOCOMPRESS, PARTITIO"
  22.   "N "P003 " VALUES LESS THAN (1000)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS"
  23.   " 255 STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST"
  24.   " GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS " LOGGING NOCOMPRESS )"
  25. . . skipping partition "PART001" : "P001"

  27. . . skipping partition "PART001" : "P002"
  28. IMP -00009 : abnormal end of export file
  29. Import terminated successfully with warnings .
  30. [oracle@oemtest ~ ] $


2、在使用直接匯出之前，充實空分割槽表

點選( 此處)摺疊或開啟

  1. 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部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-2128140/，如需轉載，請註明出處，否則將追究法律責任。

相關文章