ORA-39165: Schema SYS was not found

 Oracle  Comments Off on ORA-39165: Schema SYS was not found
Dec 132010

A quick reminder: I dont know why, but expdp 10.2 does not work with tables in the following schemas: SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP и WMSYS. I’ve hit this while trying to export sys.aud$, in order to transfer it to another database for analysis. Nope – this cannot be done in 10.2. The problem is described in Metalink Note 553402.1, as well as here

This is very hasty. One way to workaround this is using the good old exp / imp. Unfortunately they are way slower on tables with LOB columns. Also exp/imp do not work across db link, and setting up REMAP_TBLESPACE is another adventure. One other possibility is using create table as select – not a great idea for big tables and heavily loaded systems.

One more interesting fact: expdp makes more than 300 megabytes physical reads before failing with “Schema SYS was not found”.

 Posted by at 13:18