How to cleanup orphaned datapump jobs

How can I cleanup old (orphaned) datapump jobs?

Identify these jobs:

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;

 

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.

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

 

Author: Vincent Fenoll – Oracle DBA from Montreal