Deleting not running datapump jobs in oracle

This is short post I will share the steps involved in deleting not running datapump jobs in oracle databases. First of all we need to check what all jobs are not running.

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs ORDER BY 1,2;

Even if you stop a job willingly, it will show as not running, hence make sure you are deleting the right job. With every job there is an associated master table created, find out that master table with below query. Once you know the master tables, drop those tables in order to delete the datapump jobs.

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;

Thanks for reading…

subscribe to get notified for latest post.