今天上午，收到开发同事发过来的邮件：
需要将从之前用EXPDP备份的dump文件中，将某张表还原到一个新的schema下。
电话沟通后，原来是想要将逻辑备份的dump文件中FR8_ZH这个用户下的SB_DATA_RIGHT，导入到同库下的FR8_TEST9这个schema下。
① 于是开始干活：
C:\Documents and Settings\Administrator>impdp directory=my_dump dumpfile=2012-01 -23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIGHT Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:32:53 Copyright (c) 2003, 2007, Oracle. All rights reserved. 用户名: sys/oracle as sysdba 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31655: 尚未为作业选择数据或元数据对象 已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01" 启动 "SYS"."SYS_IMPORT_TABLE_01": sys/******** AS SYSDBA directory=my_dump dump file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S B_DATA_RIGHT 作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:33:19 成功完成
发现，报出ORA-31655的错误！！！
而
SQL> !oerr ora 31655 31655, 00000, "no data or metadata objects selected for job" // *Cause: After the job parameters and filters were applied, // the job specified by the user did not reference any objects. // *Action: Verify that the mode of the job specified objects to be moved. // For command line clients, verify that the INCLUDE, EXCLUDE and // CONTENT parameters were correctly set. For DBMS_DATAPUMP API // users, verify that the metadata filters, data filters, and // parameters that were supplied on the job were correctly set. SQL>
而ORA-31655的错误是说，在导入数据的命令中，impdp没有找到正确的对象元数据。
② 加上INCLUDE关键字，重新执行导入：
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012- 01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIG HT include=table:\"like \'SB_DATA_RIGHT%\'\" Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:36:35 Copyright (c) 2003, 2007, Oracle. All rights reserved. 用户名: sys/oracle as sysdba 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31655: 尚未为作业选择数据或元数据对象 已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01" 启动 "SYS"."SYS_IMPORT_TABLE_01": sys/******** AS SYSDBA directory=my_dump dump file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S B_DATA_RIGHT include=table:"like \'SB_DATA_RIGHT%\' " 作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:37:02 成功完成
发现依然报ORA-31655的错误！
③ 不得已，查找Metalink，ORA-31655 On imdp As a Privileged User Wih INCLUDE= After Upgrade To 10.2.0.5/11.2 [ID 1225108.1] 得到下述的解决办法：
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later [Release: 10.2 and later ]
Oracle Server – Enterprise Edition – Version: 11.2.0.1.0 to 11.2.0.2.0 [Release: 11.2 to 11.2]
Information in this document applies to any platform.
Symptoms
Datapump import raises an ORA-31655 “no data or metadata objects selected for job” on import when using the REMAP_SCHEMA and INCLUDE= clauses when performed as a privileged user (i.e. a user with the IMP_FULL_DATABASE role).
Changes
The database has been upgraded to 10.2.0.5 or 11.2.
Cause
This is intended behavior and occurs due to the fix for bug:6831823 which went in to 11.2 and 10.2.0.5.
The ORA-31655 error is now signaled following the fix for bug:6831823, as that bug which existed in previous versions meant that if a privileged user performed a table import from a full export dump, and the specified table name existed in multiple schemas, then all the tables would be imported where as only the table for the importing user should have been imported, as documented in the Oracle� Database Utilities 10g Release 2 (10.2) manual, chapter 3 ‘Data Pump Import’, under the definition of the TABLES= clause, which states “If you do not supply a schema_name, it defaults to that of the current user”. This also applies to the INCLUDE= clause, and hence the error is now raised.
Documentation bug:10140472 has been created to get this more clearly stated under the INCLUDE and REMAP_SCHEMA options in future documentation sets.
Solution
Perform the import using the TABLES= clause rather than the INCLUDE=TABLE: clause, e.g.:
Change From:
impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”
To:
impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test tables=SCOTT.EMP,SCOTT.DEPT
or
impdp scott directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEPT\’\)\”
找到问题的原因后，执行下述命令重新导入成功！
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012- 01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=fr8_zh.sb_d ata_right Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:48:02 Copyright (c) 2003, 2007, Oracle. All rights reserved. 用户名: sys/oracle as sysdba 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01" 启动 "SYS"."SYS_IMPORT_TABLE_01": sys/******** AS SYSDBA directory=my_dump dump file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=f r8_zh.sb_data_right 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA . . 导入了 "FR8_TEST9"."SB_DATA_RIGHT" 5.328 MB 4484 行 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT 处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:48:49 成功完成
最后，登录数据库后，检查FR8_TEST9用户下的SB_DATA_RIGHT表中的数据，发现一切正常。