impdp 10g/11g問題解決

今天在環境上測試expdp/impdp，環境有10.2.0.5.0，11.2.0.2.0的，11g的環境是從10g升級到11gde .是在impdp的時候都報了錯誤。

10g報錯如下：

> impdp dumpfile=a.dmp directory=true_dump tables=test_table

Import: Release 10.2.0.5.0 - 64bit Production on Tuesday, 29 October, 2013 17:24:12

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  dumpfile=a.dmp directory=a_dump tables=test_table
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 17:24:15

11g報錯如下：

> impdp temp_usg/temp_usg directory=expdp_dir dumpfile=a.dmp tables=tet_table

Import: Release 11.2.0.2.0 - Production on Tue Oct 29 19:47:17 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object TEMP_USG.TEST_TABLE was not found.

最後嘗試使用remap_schema 和tables選項，還是報錯。

10g報錯：

> impdp dumpfile=a.dmp directory=a_dump remap_schema=trudbo24:test tables=test_table

Import: Release 10.2.0.5.0 - 64bit Production on Tuesday, 29 October, 2013 18:03:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  dumpfile=a.dmp directory=true_dump remap_schema=trudbo24:test tables=csm_offer
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 18:03:43

11g報錯：

Import: Release 11.2.0.2.0 - Production on Tue Oct 29 19:49:32 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object TEMP_USG.TEST_TABLE  was not found.

最後在tables的地方加上schema名稱，可以順利匯入了。
impdp test/test  dumpfile=a.dmp directory=a_dump remap_schema=testo24:test tables=testo24.test_table

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/********

dumpfile=a.dmp directory=a_dump remap_schema=testo24:test tables=testo24.test_table

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TEST_TABLE"                          1.622 MB    1939 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 18:04:33

查詢metalink，也有一個類似的bug，看來只能這樣先用了。