Wednesday, January 14, 2009

ORA-39165: Schema SYS was not found ORA-39166: Object AUD$ was not found.

I would like to use "expdp"... export sys.aud$ table. Ans I found some error...

$   expdp   directory=BACKUP  TABLES=sys.AUD$ DUMPFILE=dumpfile.dmp

Export: Release 10.2 - 64bit Production 
Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2 
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=BACKUP TABLES=sys.AUD$ DUMPFILE=dumpfile.dmp 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema SYS was not found.
ORA-39166: Object AUD$ was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 16:58:49

Expdp command can not export system schemas  like SYS, ORDSYS, and MDSYS in any mode.
So, I need to use "exp" ...

exp system/ file=dumpfile.dmp log=logfile.log tables=sys.aud$

$ exp file=dumpfile.dmp log=logfile.log tables=sys.aud$

Export: Release 10.2

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

About to export specified tables via Conventional Path ...
. . exporting table                           AUD$      10000 rows exported
Export terminated successfully without warnings.

2 comments:

Anonymous said...

I learned the hard way too... I found this Metalink Note:

Ora-39165: Schema Sys Was Not Found. [ID 553402.1]



Cause

There is a restriction on Expdp. It cannot export system schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.

The Utilities Guide indicates the restriction only on full export mode, but the restriction actually applies to all modes.



Solution

Export the aud$ table using traditional export:

exp system/ file=dumpfile.dmp log=logfile.log tables=sys.aud$

Anonymous said...

The solution is need to create new user schema and create the same table and select the sys.tables' values throguh CTAS and than try to expdp.