Wednesday, April 22, 2009

ORA-39165: Schema TMP was not found and ORA-31655: no data or metadata objects selected for job

These errors can occur if the user exists and owns objects but when there are no records in any of the tables.

Error:

expdp system/[PASSWORD]@[ORACLE_SID] directory=[EXPORT_DIR] logfile=[LOG_FILE].log content=DATA_ONLY dumpfile=[DUMP_FILE].dmp schemas=[TMP] 

Export: Release 11.1.0.6.0 - Production on Wednesday, 22 April, 2009 16:26:37

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

Connected to: Oracle Database 11g Release 11.1.0.6.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@[ORACLE_SID] directory=[EXPORT_DIR] logfile=[LOG_FILE].log content=DATA_ONLY dumpfile=[DUMP_FILE].dmp schemas=[TMP]
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema [TMP] was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 16:26:41


Solution:
Remove the DATA_ONLY option, only the METADATA will be exported!!

Did this post help you in any way can you please leave a comment? This will motivate me writing more posts.
on Wednesday, April 22, 2009

1 comment:

  1. yumNovember 19, 2009 at 5:53 PM

    ORA-39165: Schema [TMP] was not found. can also can also come for without option "content=DATA_ONLY" ..

    didn't worked in my case.

    expdp USERID=system DUMPFILE=xxx.sql LOGFILE=xxx.log SCHEMAS=XXX

    impdp system DUMPFILE=xxx.sql LOGFILE=xxxx.log schemas=YYY remap_schema=XXX:YYY table_exists_action=REPLACE

    let me know if have any idea..

    ReplyDelete

comment

Subscribe to: Post Comments (Atom)