Today I had a problem with Oracle Datapump which challenged me a bit. A cloning tool uses DBMS_DATAPUMP in a PL/SQL package to copy all user schemas between two databases. The proces  was run via Toad but was cancelled by mistake before it could complete. When restarting the job, an error was returned that the job already existed ORA-31626.

Using the DBA_DATAPUMP_JOBS view I could see the job which was hanging in state of “Defining”.

I then tried to attach to the Datapump-job using DBMS_DATAPUMP.ATTACH and received the jobid.

But when I then tried to kill the job, I got an error that the job did not exist, even though I could see it using the DBMS_DATAPUMP_JOB view.

Finally, I found the DBA_DATAPUMP_SESSIONS view and I could see the killed session still was attached to the job. Using the SADDR column and a lookup in V$SESSION gave me the sessionid, which allowed me to kill the attached session.

Afterwards the job was gone and the cloning job could be restarted.