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 8: 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]