In case of original export we could easily export the tables those were inside under SYS schema.
But whenever you try to export a table from sys schema using expdp it fails with ORA-39166: Object was not found. With a simple example the scenario is demonstrated below.
1)Log on as sysdba.
SQL> conn / as sysdba
Connected.
2)Create a test table and insert data into it.
SQL> create table test_export_for_sys(value1 number);
Table created.
SQL> insert into test_export_for_sys values(55);
1 row created.
SQL> commit;
Commit complete.
3)Try to take a data pump export of this table.
SQL> host expdp userid=\"/ as sysdba\" dumpfile=sys_test.dmp tables=test_export_for_sys
Export: Release 11.1.0.6.0 - Production on Saturday, 11 July, 2009 15:01:39
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": userid="/******** AS SYSDBA" dumpfile=sys_test.dmp tables=test_export_for_sys
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object TEST_EXPORT_FOR_SYS was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:01:45
But in the database there exists test_export_for_sys table,
SQL> desc test_export_for_sys
Name Null? Type
----------------------------------------- -------- -------------
VALUE1 NUMBER
SQL> select * from test_export_for_sys;
VALUE1
----------
55
SQL> show user;
USER is "SYS"
If you try to export schema also no tables are exported.
SQL> host expdp userid=\"/ as sysdba\" dumpfile=sys_test_schema.dmp schemas=sys
Export: Release 11.1.0.6.0 - Production on Saturday, 11 July, 2009 19:14:56
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": userid="/******** AS SYSDBA" dumpfile=sys_test_schema.dmp schemas=sys
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
D:\APP\ARJU\ADMIN\ARJU\DPDUMP\SYS_TEST_SCHEMA.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:16:12
But this is not the fact in case of original export. Here is the original export output,
SQL> host exp userid=\"/ as sysdba\" file=sys_test.dmp tables=test_export_for_sys
Export: Release 11.1.0.6.0 - Production on Sat Jul 11 17:42:29 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_EXPORT_FOR_SYS 1 rows exported
Export terminated successfully without warnings.
Solution of the Problem
This is the restriction imposed in oracle data pump. A number of system schemas tables cannot be exported because they are not user schemas, they contain Oracle-managed data and metadata. As in every schemas there by default system schemas exist. Data pump utility designed for transferring data, not the database; so not the system schemas. However if you want to export sys tables like SYS.AUD$ then first transfer that table into non-restricted schema and export the table from non restricted schema.
Related Documents
No comments:
Post a Comment