Killing a Data Pump Job

<- DataPump

Standard Method

Determine the active Data Pump job name.

SQL> SELECT j.owner_name, j.job_name, 
            j.job_mode, j.state, s.session_type, s.saddr
     FROM dba_datapump_jobs j,dba_datapump_sessions s
     WHERE UPPER(j.job_name) = UPPER(s.job_name);

Owner     JOB_NAME        JOB_MODE     STATE        SESSION_TYPE    SADDR
--------- --------------- ------------ ------------ --------------- --------
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    WORKER          AD09C2D8
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    MASTER          AD09E8A8
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    DBMS_DATAPUMP   AD0BFA08

Issue the command to kill job.

 OS> expdp system/password attach=xSCHEMA
 Export> kill_job
 Export> Are you sure you wish to stop this job ([yes]/no): yes

If this is an import then use impdp correspondingly.

Via SQL

DECLARE
   h1 NUMBER;
   BEGIN
      h1:=DBMS_DATAPUMP.ATTACH('JOBNAME','SYSTEM');
      DBMS_DATAPUMP.STOP_JOB (h1, 1, 0);
   END;
/

• Set JOBNAME to your Data Pump jobname.
• Change SYSTEM user, to the user performing Data Pump operation.

Advanced Method

If the standard method does not work and the job is no longer active.

1. Log into SQLPlus as SYS.
2. Confirm JOB_NAME and OWNER.
3. Drop the corresponding job table.

DROP TABLE <schema user who started job>.<JobTableName>;

sqlplus sys/password as sysdba

COL owner_name FORMAT a15
COL job_name   FORMAT a30
COL operation  FORMAT a15
SELECT owner_name, job_name, operation,state FROM dba_datapump_jobs;

OWNER_NAME      JOB_NAME        OPERATION      STATE
--------------- --------------- -------------- --------------
SYSTEM          XSCHEMA         EXPORT         NOT RUNNING

DROP TABLE SYSTEM.XSCHEMA;

<- DataPump