EXPDP – ORA-39166: Object XXX was not found.

SQL> create table scott."test" as select * from scott.dept;

Table created.

SQL> select * from scott.TEST;
select * from scott.TEST
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from scott.test;
select * from scott.test
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from scott."test";

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Now, lets try to take an export of this table using expdp

D:\scripts>expdp scott/tiger directory= DATA_PUMP_DIR dumpfile=table_test.dmp logfile=table_test.log tables=test

Export: Release 11.2.0.2.0 - Production on Thu Jul 21 21:45:27 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory= dumpfile=table_test.dmp logfile=table_test.log tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SCOTT.TEST was not found.
ORA-31655: no data or metadata objects selected for job
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 21:45:30

Opppssss, i have hit ORA-39166 and ORA-31655. To take the export of such table use

D:\scripts>expdp scott/tiger directory= DATA_PUMP_DIR dumpfile=table_test.dmp logfile=table_test.log tables='"""test"""'

Export: Release 11.2.0.2.0 - Production on Thu Jul 21 21:53:22 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory= dumpfile=table_test.dmp logfile=table_test.log tables='"test"'
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."test"                              5.937 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\APP\ADMIN\MATRIX\DPDUMP\TABLE_TEST.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 21:53:30

Open 1 single quotes followed by 3 double quotes ans close them.

In case expdp of the tables (not created suing quotes) gives ORA erros trace the expdp using TRACE=480300 which might be helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s