Data pump orphan jobs cleanup.

when export/import job fail time it exit from procees level but still not in dabase.
But it does not effect your next job job or current job it give another job name like _02 runs.
only impact is still it occupies space in db level so better check and  clean up orphan jobs from database to free up system table space.




1.For now we don’t have export job is running but as checked found oracle orphaned datapump job ,  in other database not found like this
SQL>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;

-- output for refrence
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
SYS SYS_EXPORT_FULL_01 EXPORT                        FULL                          NOT RUNNING 0

select count(*) from sys.SYS_EXPORT_FULL_01
8535

2. So, we have to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS by drop:

SQL> DROP TABLE SYS.SYS_EXPORT_FULL_01;   ==> make sure current table-name.



select sum(bytes/1024/1024) from dba_segments where segment_name = 'SYS_EXPORT_FULL_01' and owner = 'SYS'
3MB

select owner,created,object_name,last_ddl_time from dba_objects where object_name = 'SYS_EXPORT_FULL_01'
OWNER CREATED OBJECT_NAME LAST_DDL_TIME
SYS 12/23/2015 17:13 SYS_EXPORT_FULL_01 12/23/2015 17:13

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 26326 TABLE SYS.SYS_EXPORT_FULL_01