0

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
1

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?

3
  • can you add a reference for "Oracle actually recommends that you do not export the statistics" – miracle173 Mar 27 '20 at 19:57
  • We don't have a dedicated data pump best practices document published on www.oracle.com (yet) but it is in the slides we use for various presentations. Go to www.mikedietrichde.com > slides > "Upgrade / Migrate / Consolidate to 19c". It is on the "Best Practice #3" slide (on page 392 or thereabout). – Daniel Overby Hansen Mar 29 '20 at 7:17
  • thank you, I found it (page 391) – miracle173 Mar 30 '20 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.