Home / server / Questions / 1003429
Accepted
Jarmund
Asked: 2020-02-18 11:29:42 +0800 CST

Oracle database schema export

  • 772

I'm trying to run a schema export through expdep on an oracle database:

expdp somedatabase/something directory=EXP_DIR schemas=someschema dumpfile=dumpfile.dmp logfile=logfile.log

The above command (although edited for the purpose of posting this question) has been working before, but now I get this error:

ORA-31626: job does not exist
ORA-31633: unable to create master table "SOMEDATABASE.SYS_EXPORT_SCHEMA_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1161
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
ORA-06512: at "SYS.KUPV$FT", line 1054
ORA-06512: at "SYS.KUPV$FT", line 1042

I have reason to believe that the root cause is that there are stalled jobs relating to earlier data pumps that were semi-aborted (ctrl+c) with their destination files removed.

How do I go about clearing these jobs so that I can restart the data pump from scratch?

From the looks of it, I am running version 12.2.0.1 on Centos.

data-pump oracle-linux

1 Answers

  1. Best Answer

    I managed to find a sollution buried deep inside google search results. This worked for me:

    Step 1: Find stalled jobs:

    SELECT o.status, o.object_id, o.object_type,        o.owner||'.'||object_name "OWNER.OBJECT"      FROM dba_objects o, dba_datapump_jobs j      WHERE o.owner=j.owner_name AND o.object_name=j.job_name       AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

    Step 2, Remove them:

    drop table TABLENAME_RETURNED_IN_STEP_1;

    After this I was able to do the export as before.

    • 0