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.

PROCEDURE

  • 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; 
/

ADDITIONAL INFORMATION

  • Metalink Article 336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS
© copyright 2001-2014 ABCdba.com | all rights reserved