Problem Description
This is a variant of error described in ORA-39166: Object was not found, SYS tables can't be exported. The ORA-39166 throws if you want to take data pump export of SYS objects using SYS user. And ORA-39165 throws if you want to take data pump export of SYS objects as a non-SYS user.
With a simple example problem is demonstrated here.
SQL> conn / as sysdba
Connected.
SQL> create table database_10g(col1 number);
Table created.
SQL> insert into database_10g values(23);
1 row created.
SQL> commit;
Commit complete.
SQL> host E:\oracle\product\10.2.0\db_2\BIN\expdp userid=arju/a tables=sys.database_10g dumpfile=sys_table_test.dmp
Export: Release 10.2.0.1.0 - Production on Saturday, 11 July, 2009 18:30:26
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": userid=arju/******** tables=sys.database_10g dumpfile=sys_table_test.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 DATABASE_10G was not found.
ORA-31655: no data or metadata objects selected for job
Job "ARJU"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 18:30:36
It says both schema SYS and object table does not exist. But actually both are existed. Here is the proof.
SQL> conn arju/a
Connected.
SQL> desc sys.database_10g
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
SQL> select * from sys.database_10g ;
COL1
----------
23
SQL> select table_name, owner from dba_tables where table_name='DATABASE_10G';
TABLE_NAME OWNER
------------------------------ ------------------------------
DATABASE_10G SYS
Cause of the Problem
There is a restriction imposed in data pump export that SYS tables, objects are not exported even with full export option. Whenever we export by schemas=sys then role grants are exported but no data. Data pump does not allow to export system schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.
Solution of the Problem
1)Use original export instead of data pump export to export SYS objects/schemas.
2)First using create table as select transfer SYS objects into non-restrictive schema and using data pump export data/tables from non-restrictive schema.
So the conclusion is the SYS schema, SYS tables cannot be used as a source schema for data pump export jobs.
Related Documents
not understood
ReplyDelete