Applies to:
Oracle Database – Enterprise Edition – Version 10.2.0.5 to 11.2.0.3 [Release 10.2 to 11.2] Information in this document applies to any platform. *Checked for relevance on 02-MAR-2012*
Symptoms
DataPump import raises the error ORA-31655 – no data or metadata objects selected for job – when using the REMAP_SCHEMA and INCLUDE parameters performed as a privileged user (i.e. user with IMP_FULL_DATABASE role granted).
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 into 11.2 and 10.2.0.5. The ORA-31655 error is now signaled due to the fix for bug 6831823. 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 are imported instead of only the table for the importing user, like documented in Oracle Database Utilities, Data Pump Import, under the definition of the TABLES clause: "If you do not supply a schema_name, it defaults to that of the current user". This also applies to the INCLUDE clause. Documentation bug 10140472 has been created, too.
Solution
Perform the import using the TABLES parameter rather than the INCLUDE=TABLE:"= '", e.g.: – change from:
#> impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:"IN ('EMP', 'DEP')"
| | Symptoms
Changes
Cause
Solution
References
to one of the following:
#> impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test tables=SCOTT.EMP,SCOTT.DEPT (use the TABLES= clause)
#> impdp scott directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:"IN ('EMP', 'DEPT')" (perform the import as the schema user)
#> impdp system directory=data_pump_dir dumpfile=scott.dmp schemas=scott remap_schema=scott:scott_test include=TABLE:"IN ('EMP', 'DEP')" (specify the SCHEMAS option)
References
BUG:6831823 – THE IMPDP TABLES PARAMETER DOES NOT YIELD THE EXPECTED RESULTS