How To Tidy Old Datapump Jobs
BACKGROUND & OVERVIEW
If a datapump job fails, and cannot or does not need to be restarted, there will be remnants of the jobs left in the database.
This document descibes the way to remove those remnants.
- As SYS, identify which jobs exist in the database:
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' AND state = 'NOT RUNNING' ORDER BY 1,2 /
- As SYS, delete any Stale jobs
DROP TABLE <owner_name>.<job_name> /
- If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. For example:
CONN scott/tiger DECLARE h1 NUMBER; BEGIN h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT'); DBMS_DATAPUMP.STOP_JOB (h1); END; /
- Metalink Article 336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS