How to Kill a datapump job and cleanup orphaned jobs in Oracle

Take Control: True Private Internet Browsing‎ with Brave Download the Brave Browser ⇒ A safer and faster alternative than Chrome.

It’s also free and you support my website


In this article you will learn how to:

  • Kill a datapump job from  dba_datapump_jobs using expdp or with an internal package
  • Cleanup orphaned datapump jobs in Oracle

Perhaps you were looking for stop/drop job in DBMS_SCHEDULER.


How to kill a datapump job in DBA_DATAPUMP_JOBS?

You can kill an oracle datapump job using two methods:
– First method includes killing data pump job via data pump export prompt
– The second method is by running a SQL package in SQL*Plus.

Cancel a datapump job in Oracle

1- Kill Oracle datapump export job from expdp

— Find the job name of the datapump export
select job_name from dba_datapump_jobs;

-- Connect to datapump export and Kill the job
$ expdp system/my-password@ORCL attach=SYS_EXPORT_FULL_05
Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes

2- How to Interrupt and Kill a Data Pump Job from a SQL package

— Find the job name of the datapump export
select job_name, owner_name from dba_datapump_jobs;

-- Connect to datapump export and Kill the job

After stopping/cancelling the datapump job, oracle removes the dmp files that have been produced. You can check again the dba_datapump_jobs view to verify:
SQL> select * from dba_datapump_jobs;

More info on view dba_datapump_jobs in official the Oracle Documentation.


How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?

Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.

Error often raised by expdp:

ORA-31626: job does not exist
ORA-31633: unable to create master table « SYS.SYS_EXPORT_FULL_01 »
ORA-06512: at « SYS.DBMS_SYS_ERROR », line 95
ORA-06512: at « SYS.KUPV$FT », line 1163
ORA-00955: name is already used by an existing object
ORA-06512: at « SYS.KUPV$FT », line 1056
ORA-06512: at « SYS.KUPV$FT », line 1044


Below is a step-by-step instruction on how to resolve it.

Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

Step 2: Drop the master tables

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

Step 3: Identify orphan DataPump external tables

Check  and drop external tables created for datapump jobs with select  object_name, created from dba_objects where object_name like ‘ET$%’

Step 4: Purge recycle bin

If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘ »‘ || job_name || ‘ »;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

Step 5: Confirm that the job has been removed

Run sql statement from step 1.

Now you should be able to run your script with the same job name without any issues.

Hope this post will help!

Author: Vincent Fenoll – Oracle DBA

Compatibility:  Oracle Database – Standard/Enterprise Edition – Version 10g to 18c [Release 10.1 to 12.2/18]

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.