ora 39002 oracle11gr2，,ORACLE11GR2 IMPDP With REMAP_SCHEMA Fails With Errors ORA-39002 ORA-39166

you specify a list of tables with TABLES parameter and use REMAP_SCHEMA parameter. For example, your impdp parameter file looks like:

tables=CRM_COMPLAINT_PROBLEM

remap_schema=ytcrm:yto_uat

the import fails with the following error messages:

ORA-39002: invalid operation

ORA-39166: Object SCOTT_NEW.EMP was not found.

ORA-39166: Object SCOTT_NEW.DEPT was not found.

DataPump Import does no longer work with a list of tables given in the parameter file, if you remap the schema.

It searches the list of tables in the target schema and throws an error saying that the table does not exist.

For example:

[oracle@trsen ~]$impdp yto_uat/yto_uat tables=CRM_COMPLAINT_PROBLEM directory=USER_EXPDP TABLE_EXISTS_ACTION=append CONTENT=DATA_ONLY dumpfile=CRM_COMPLAINT_PROBLEM.dmp remap_tablespace=TS_YTCRM:YTO_UAT_DATA remap_schema=ytcrm:yto_uat

then impdp fails with:

Import: Release 11.2.0.3.0 – Production on Wed Apr 29 09:59:27 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39166: Object YTO_UAT.CRM_COMPLAINT_PROBLEM was not found.

ORA-39002: invalid operation

ORA-39166: Object DEV.EMPLOYEES was not found.

In this case, the use of “TABLES=EMPLOYEES” is incorrect. The job runs as “DEV” and a schema for the table was not specified. The DataPump job assumes that the table belongs to the schema that is running the job. So, it is looking for “DEV.EMPLOYEES”. If you want “HR.EMPLOYEES” to be imported, you need to specify TABLES=HR.EMPLOYEES.

SOLUTION

1、provide old schema.table_name

tables=ytcrm.CRM_COMPLAINT_PROBLEM

remap_schema=ytcrm:yto_uat

2、don’t the “tables” parameter

impdp yto_uat/yto_uat directory=USER_EXPDP TABLE_EXISTS_ACTION=append CONTENT=DATA_ONLY dumpfile=CRM_COMPLAINT_PROBLEM.dmp remap_tablespace=TS_YTCRM:YTO_UAT_DATA remap_schema=ytcrm:yto_uat