HowTo Stop/Kill Oracle Data Pump Jobs

When running Oracle Data Pump, the prompt (interactive mode) can be accessed by pressing the Ctrl-C Keys.

Here the import case as an example:

Import> stop_job=IMMEDIATE

If you are not in the interactive mode of Data Pump (you are in the OS level on the console), you can reattach to the export / import job running in background by recalling the correspontent binary (i.e. If you was performing an import, call impdp. If you was performing an export, call expdp.) and give the name of the Data Pump job as parameter to "ATTACH=":

OS> impdp ATTACH=SYS_IMPORT_SCHEMA_01

Import> stop_job=IMMEDIATE

or

Import> kill_job

If you do not know the name of the Data Pump job, you can perform the following query on the DB:

SQL> SELECT * FROM dba_datapump_jobs WHERE state <> 'NOT RUNNING';

=> JOB_NAME: SYS_IMPORT_SCHEMA_01

How To Cleanup Orphaned Data Pump Jobs

List Data Pump jobs:

set lines 999

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

  FROM dba_datapump_jobs

 WHERE job_name NOT LIKE 'BIN$%'

 ORDER BY 1,2;

-- Query 1 --

List the Data Pump master tables:

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;

-- Query 2 --

Drop the correspondent table:

DROP TABLE xxxx;

Re-run the query on dba_datapump_jobs and dba_objects.

If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. (Adapt the highlighted parameter) E.g.:

SET serveroutput on

SET lines 100

DECLARE

   h1 NUMBER;

BEGIN

   h1 := DBMS_DATAPUMP.ATTACH('xxxx','SYSTEM');

   DBMS_DATAPUMP.STOP_JOB(h1);

END;

/

-- PL/SQL 1  --

Killing all Data Pump jobs listed by -- Query 1 -- by using -- PL/SQL 1  --  (But take care!):

DECLARE

   h1    NUMBER;

   l_cur SYS_REFCURSOR;

BEGIN

   FOR l_cur IN (SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

                      FROM dba_datapump_jobs

                     WHERE job_name NOT LIKE 'BIN$%')

   LOOP

     BEGIN

      DBMS_OUTPUT.PUT_LINE('Kill job '||l_cur.job_name||'  owner: '||l_cur.owner_name||'  state: '||l_cur.state);

      h1 := DBMS_DATAPUMP.ATTACH(l_cur.job_name, 'SYS');

      DBMS_DATAPUMP.STOP_JOB(h1);

     EXCEPTION

       WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('EXCEPTION:'||sqlerrm);

     END;

   END LOOP;

END;

/

-- PL/SQL 2  --

Generates UNIX statements to kill Data Pump Linux/UNIX processes:

SELECT 'kill -9 ' ||spid , s .action , s .program, s .machine , s .osuser , s.module

  FROM v$process p, v$session s

WHERE s. paddr = p. addr

  AND (action LIKE 'CP_%' OR UPPER(module) LIKE '%PUMP%');

2017.01.19