How to Properly Kill, Stop, Delete or Clean Up Oracle Data Pump Jobs

milly 5:14 pm on May 2, 2010 | Answers: 1    
Tags: datapump (2), method (2), oracle (136), use (24)

Rate Knowledge:

 Loading ...

• milly 5:14 pm on May 2, 2010
  

  1. Get the list of datapump jobs:

  SELECT owner_name, job_name, operation, job_mode,
  state, attached_sessions
  FROM dba_datapump_jobs
  WHERE job_name NOT LIKE 'BIN$%';

  2. Take at note of the values from the following two columns relating to the jobs you want to kill, clean, or stop.

  - OWNER_NAME
  - JOB_NAME

  3. Run the following procedure to kill, stop or clean up data pump jobs:

  SET serveroutput on
  SET lines 100
  DECLARE
     h1 NUMBER;
  BEGIN
    -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
     h1 := DBMS_DATAPUMP.ATTACH('HR','SCHEMA_USER');
     DBMS_DATAPUMP.STOP_JOB (h1,1,0);
  END;
  /

  Note: replace “HR” for the job_name and “SCHEMA_USER” for the owner_name

  4. Now run the SQL on step 1 again to make sure the job has been killed.