Hi,I am getting the following error message when using impdp and trying to import just the tables that were exported using expdp.
impdp parfile=test.par
Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 23 June, 2009 11:27:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39039: Schema expression "IN (SELECT object_name FROM "SYS"."SYS_IMPORT_SCHEMA_01" WHERE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas.
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": parfile=test.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:27:20
Here is my expdp parfile:
userid="/ as sysdba"
directory=special_export
dumpfile=expdp_NON_LOB_PROD_062309_%U.dmp
logfile=expdp_NON_LOB_PROD.log
parallel=10
tables=.......
The tables parameter had about 40 tables listed as example:
ecom.customer, ecom.account, ecom.address etc......
My impdp parameter file was :
userid="/ as sysdba"
directory=special_export
schemas=ecom
dumpfile=expdp_NON_LOB_PROD_062309_%U.dmp
logfile=impdp_NON_LOB_PROD.log
parallel=10
remap_schema=ecom:ecom
TABLE_EXISTS_ACTION=REPLACE
CONTENT=ALL
When i used this impdp parameter file, i kept getting that error :
Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 23 June, 2009 11:27:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39039: Schema expression "IN (SELECT object_name FROM "SYS"."SYS_IMPORT_SCHEMA_01" WHERE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas.
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": parfile=test.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:27:20
This was very frustrating and all the help online couldn't fix it . .. because all i wanted to do was import just the tables... and all of them not specific ones. So i tried a few things manually and here is the fix :
In the impdp parfile add this to the bottom:
INCLUDE=TABLE
This will include all the tables in the exported dump files and the error message went away. Basically what you have to do is to make sure you put in any of the following: INCLUDE, EXCLUDE, CONTENT etc.. . otherwise it'll throw that error message.
Hope this helps.
Thanks,