EXPDP error ORA-39374 + ORA-13158 Oracle object does not exist

expdporacle-12c

While exporting one of the tables in my db I got the following error :

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39374: Statistics failed to export. Failing error is
ORA-13158: Oracle object 3752503189677886288 does not exist
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

After this on the general print out where my export is running I saw . . Exported TABLE_NAME 51.31 GB 273283803 rows however the same is not printed in the expdp log..

Also the export command failed to return for almost 8 hours after which I forcefully tried to close it with Ctrl-C + Export> kill_job which also did not close the export session in a clean way.

dba_datapump_jobs:

+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+
| OWNER_NAME | JOB_NAME            | OPERATION | JOB_MODE | STATE     | DEGREE | ATTACHED_SESSIONS | DATAPUMP_SESSIONS |
+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+
| APPUSER    | SYS_EXPORT_TABLE_02 | EXPORT    | TABLE    | EXECUTING | 1      | 0                 | 2                 |
+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+

I am unable to figure out why my export job failed and more importantly how to close the active sessions in a clean way..

expdp command

expdp user/pass directory=TEST_DIR dumpfile=xxx.dmp logfile=xxx.log tables=TABLE_NAME

Best Answer

It is recommended to exclude statistics when doing an export. It is better to export the stats in a different way (DBMS_STATS) or re-gather once the data is imported. I know it does not solve the root cause, but Oracle actually recommends that you do not export the statistics.

Try to do the export again with LOGTIME=ALL and METRICS=Y to get a better idea of what it is spending time on. Otherwise, you might have to turn on 10046 tracing to see what query it is stuck in.

Do you have many objects (10.000+) to export or many partitions?

Related Solutions

Expdp error UDE-22303

SQL> show parameter aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0

SQL> show parameter streams_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 24G

Solution:

SQL> alter system set aq_tm_processes=1 scope=both sid='*';
SQL> alter system set streams_pool_size = 256M scope = both sid='*';

If you get any error like this:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

Reduce the db_cache_size:

SQL> alter system set db_cache_size = 12G scope = both sid='*';

and re-run:

SQL> alter system set streams_pool_size = 256M scope = both sid='*';

Now, you can run the expdp again without issues. Once done, don't forget to roll back all changes.

Oracle 12c – Installing expdp and impdp on Linux

Data Pump utilities are installed automatically during the installation of Oracle Database. It should be working without any issues as long as the environment variables(PATH, ORACLE_HOME) are set properly. To set PATH and ORACLE_HOME variable permanently-

1) Log in as oracle user in the OS.

2) [oracle@ora12c Desktop]$ vi ~/.bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1; export ORACLE_HOME
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin

3)[oracle@ora12c Desktop]$ . ~/.bash_profile

4)Check the value of the variables.

[oracle@ora12c Desktop]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@ora12c ~]$ echo $PATH
.:/usr/local/java/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db_1/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin

5) Try to run expdp

[oracle@ora12c Desktop]$ expdp 

Export: Release 12.1.0.2.0 - Production on Tue Oct 4 16:46:44 2016

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

Username:

Setting Oracle Environment Variables

Best Answer

Related Solutions

Expdp error UDE-22303

Oracle 12c – Installing expdp and impdp on Linux

Related Question