So for various reasons I run into orphaned datapump jobs either due to a session failure during the import or for some other unknown reason for a failed import. What I wanted to show you really quick was how to find them and how to remove them so you can run your datapump job without changing the name of the job in your script or on the command line.
First you need to find the orphaned jobs with the below query.
select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs;
After you run the above query you should see a similar output below.
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
SYSTEM XPORT_APP01_01 EXPORT SCHEMA EXECUTING 0
SYS SYS_EXPORT_REP01_03 EXPORT SCHEMA NOT RUNNING 0 0
What you are looking for is the JOB_NAME that you specified during the import. In the STATE column you are looking for a status of ‘NOT_RUNNING’. This is an orphaned job.
To remove the orphaned job you simply need to drop the master table created when an import happens. It’s as easy as:
drop table SYS_EXPORT_REP01_03 purge;
This command will drop the associated master table with the failed import job. Now you should be able to run your script with the same job name without any issues.
Hope this helps someone with a slight frustration I sometimes have with failed datapump imports.
Great Job. This article helps me a lot.
Thank you Xtivia!