Kill, cancel, resume or restart datapump expdp and impdp jobs (ORA-31626, ORA-31633, ORA-06512, ORA-00955)

The expdp and impdp utilities are command-line driven, but when starting them from the OS-prompt, one does not notice it. When you run impdp or expdp and use ‘ctrl-c’ and you want to kill, cancel, start or resume a job, you will end up in the datapump command prompt… now what?!

All command shown here can be used with expdp and impdp datapump.

Identifying datapump jobs

Do a select from dba_datapump_jobs in sqlplus to get the job name, this is useful when you hit errors when a master table already exists (see ATTACH):

> impdp system (...)

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.<TABLE_NAME>"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

SQL> 
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING

Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…

expdp system full=y JOB_NAME=EXP_FULL

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       EXECUTING

Killing or stopping a running datapump job

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!

When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB or STOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…

Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

Resuming a stopped job

Identify your job with SQL or you already knew it because you used ‘JOB_NAME=‘ ;)

SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       NOT RUNNING

Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

> expdp system ATTACH=EXP_FULL

Job: EXP_FULL
 Owner: SYSTEM
 Operation: EXPORT
 Creator Privs: TRUE
 GUID: A5441357B472DFEEE040007F0100692A
 Start Time: Thursday, 08 June, 2011 20:23:39
 Mode: FULL
 Instance: db1
 Max Parallelism: 1
 EXPORT Job Parameters:
 Parameter Name      Parameter Value:
 CLIENT_COMMAND        system/******** full=y JOB_NAME=EXP_FULL
 State: IDLING
 Bytes Processed: 0
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: /u01/app/oracle/admin/db1/dpdump/expdat.dmp
 bytes written: 520,192

Worker 1 Status:
 Process Name: DW00
 State: UNDEFINED

(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current ‘work item’ datapumo was working on. To show progress again, type CONTINUE_CLIENT.

Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT
Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26
Restarting "SYSTEM"."EXP_FULL":  system/******** full=y JOB_NAME=EXP_FULL

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE

Done…

Happy pumping!

Tagged , , , . Bookmark the permalink.

22 Responses to Kill, cancel, resume or restart datapump expdp and impdp jobs (ORA-31626, ORA-31633, ORA-06512, ORA-00955)

  1. Vimal says:

    Thanks!!!

  2. Carlos says:

    Very Usefull…. Thanks

  3. vincent says:

    merci beaucoup pour l’astuce !

  4. Senthil says:

    Good Article

  5. Ashutosh Khare says:

    Thanks for sharing the document.

  6. good jobs says:

    MOre interesting if you cut the terminal it’s also working…. just check log file
    happy dumpingg…………

  7. Excellent job…………

  8. vinil says:

    Very nice,usefull….

  9. Oscar Pachon says:

    Excelente aporte, muchas gracias

  10. bash says:

    thnx

  11. Alexis LEGROS says:

    Thanks a lot, you saved my day ! :-)

  12. jihel says:

    Many Thanks, my server crashed after more than 24hoours of export !!!
    I can resume my export thanks to your procedure

    Regards

  13. hashim says:

    thanks

  14. Prakash says:

    Thanks

  15. abhi says:

    Very nicely written,Thanks for the help.

  16. Tom says:

    We have had issues with expdp processes. The dba_datapump_jobs table has the following:

    OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
    ———- ——————- ——— ——– ———– —————–
    SYSTEM SYS_EXPORT_TABLE_01 EXPORT TABLE EXECUTING 0
    SYSTEM SYS_EXPORT_TABLE_11 EXPORT TABLE EXECUTING 0
    SYSTEM SYS_EXPORT_TABLE_12 EXPORT TABLE EXECUTING 0
    SYSTEM SYS_EXPORT_TABLE_13 EXPORT TABLE EXECUTING 0
    SYSTEM SYS_EXPORT_TABLE_14 EXPORT TABLE EXECUTING 0
    SYSTEM SYS_EXPORT_TABLE_15 EXPORT TABLE EXECUTING 0
    SYSTEM SYS_EXPORT_TABLE_16 EXPORT TABLE EXECUTING 0

    But the associated tables and no longer exist and there is nothing to attach to.

    • Ian Hoogeboom says:

      You might want to check Oracle support note 336014.1 about Orphaned Data Pump jobs.
      It’s a pretty well explained document with 8 steps to get rid of them.
      It basically comes to join dba_datapump_jobs with dba_objects and find the internal jobs tables and drop those.

  17. Kevin says:

    Thank you Ian, great info.

  18. But what happens if you have job running and bounce the database, this job will be resumable?

    manual, or automatic, or will be lost?

    i test it an can be restarted manually.

    • Ian Hoogeboom says:

      Well, the job stopped working and everything not commited will be rolled back. The job definition is still there so you can manually start it again or it will run on next moment of scheduling.

Leave a Reply

Your email address will not be published. Required fields are marked *