Friday, October 12, 2012

Cleanup Orphaned DATAPUMP jobs

Some times you cannot stop / kill a datapump job using the attach command , possibly with an error , and its status can be undefined or not running either on EM or in dba_datapump_jobs. If you are sure that this job must be cleared then perform the following as sys:



connect sys as sysdba on SQLPLUS

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;



e.g

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0 


-- locate Data Pump master tables:

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;


STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85215 TABLE SCOTT.SYS_EXPORT_TABLE_01


-- drop the master table

DROP TABLE scott.sys_export_table_01 purge;


4 comments:

  1. Very informative feature indeed, i have come across a similar solution at an ERP Job Board

    ReplyDelete
  2. It helped me keep sharing your knowledge. Thanks

    ReplyDelete
  3. Thanks for sharing such a valuable information.This post is very useful for me.Oracle Jobs

    ReplyDelete