ORACLE – Kill, stop and resume datapump expdp and impdp jobs

Since datapump export and important utilities are both command-line driven, it is easy to forget that they are runnig in the background. So sometimes you need to kill, cancel or restart the expdp or impdp job. Keep reading to see what you need to do to achieve this:

1) Identify the datapump jobs (or check the status of your job)

You can do 2 things:

a) If you assigned a name to job_name parameter of your expdp or impdp command, then you can use the name

b) You can do a select from dba_datapump_jobs to get a list of all the jobs that are currnetly running (sqlplus)

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

OWNER_NAME     JOB_NAME              OPERATION   JOB_MODE    STATE
----------     --------------------  ----------  ----------  ------------
SYS            TS2_expdp_July4       EXPORT      FULL        EXECUTING

2) Killing or stopping a running datapump job

a) To kill a job (no chance of resuming it later on)
press Ctrl-c to show the datapump prompt and then type KILL_JOB

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB

b) To Stop a job (you can resume it later)
press Ctrl-c to show the datapump prompt and then type STOP_JOB[=IMMEDIATE]

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

NOTE: Sometimes the job is killed but it’s not removed from the dba_datapump_jobs table. In that case you need to drop the table:

DROP TABLE SYS.TS2_expdp_July4;
Table dropped.

3) Resuming a stopped job

If you know the job_name parameter then use it, if you don’t do a select from dba_datapump_jobs

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

OWNER_NAME  JOB_NAME              OPERATION   JOB_MODE    STATE
----------  --------------------  ----------  ----------  ------------
SYS         TS2_expdp_July4       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=TS2_expdp_July4

One comment on “ORACLE – Kill, stop and resume datapump expdp and impdp jobs

  1. arpit

    Could you please tell me , when we resume a stopped job , how does Oracle identifies from where it will resume . Does it picks any information from any table that 5 out of 8 tables have been exported and it has to resume from 6th table !!

Leave a reply