How to Kill a datapump job and cleanup orphaned jobs in Oracle

Download Brave : Secure, Fast & Private Browser with Adblocker

In this article you will learn how to:

  • Kill a datapump job from  dba_datapump_jobs using expdp or with an internal package
  • Cleanup orphaned datapump jobs in Oracle

Perhaps you were looking for stop/drop job in DBMS_SCHEDULER.

 

How to kill a datapump job in DBA_DATAPUMP_JOBS?

You can kill an oracle datapump job using two methods:
– First method includes killing data pump job via data pump export prompt
– The second method is by running a SQL package in SQL*Plus.

Cancel a datapump job in Oracle

1- Kill Oracle datapump export job from expdp

— Find the job name of the datapump export
select job_name from dba_datapump_jobs;
==> SYS_EXPORT_FULL_05

-- Connect to datapump export and Kill the job
$ expdp system/my-password@ORCL attach=SYS_EXPORT_FULL_05
Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes

2- How to Interrupt and Kill a Data Pump Job from a SQL package

— Find the job name of the datapump export
select job_name, owner_name from dba_datapump_jobs;
==> SYS_EXPORT_FULL_05 and SYS

-- Connect to datapump export and Kill the job
DECLARE
v1 NUMBER;
BEGIN
v1:=DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_05','SYS');
DBMS_DATAPUMP.STOP_JOB (v1,1,0);
END;
/

After stopping/cancelling the datapump job, oracle removes the dmp files that have been produced. You can check again the dba_datapump_jobs view to verify:
SQL> select * from dba_datapump_jobs;

More info on view dba_datapump_jobs in official the Oracle Documentation.

 

How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?

Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.

Below is a step-by-step instruction on how to do this.

Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

Step 2: Drop the master tables

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

Step 3: Identify orphan DataPump external tables

Check  and drop external tables created for datapump jobs with select  object_name, created from dba_objects where object_name like ‘ET$%’

Step 4: Purge recycle bin

If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘”‘ || job_name || ‘”;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

Step 5: Confirm that the job has been removed

Run sql statement from step 1.

Now you should be able to run your script with the same job name without any issues.

Hope this post will help!

Author: Vincent Fenoll – Oracle DBA

Compatibility:  Oracle Database – Standard/Enterprise Edition – Version 10g to 18c [Release 10.1 to 12.2/18]

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.