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.