Oracle Data Pump: How to Remove Orphaned Jobs from the Database

This post is also available in:

Orphaned Data Pump jobs are a common occurrence in Oracle environments. They appear when an import or export session is abruptly interrupted, whether by a dropped connection, a killed session, or a server failure. Oracle creates the job’s master table in the user’s schema, but with no active process to finalize it, the job remains stuck with a NOT RUNNING status.

Beyond cluttering DBA_DATAPUMP_JOBS, these jobs can conflict with future runs if Oracle attempts to reuse the same job name.

Diagnosis

Query active and orphaned jobs from DBA_DATAPUMP_JOBS:

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

Expected output with orphaned jobs:

OWNER_NAME      JOB_NAME                       OPERATION  JOB_MODE  STATE        ATTACHED_SESSIONS
--------------- ------------------------------ ---------- --------- ------------ -----------------
EXPORT          SYS_IMPORT_TABLE_02            IMPORT     TABLE     NOT RUNNING                  0
SYS             M_IMP_COPY_1521305009006       IMPORT     TABLE     NOT RUNNING                  0

OWNER_NAME      JOB_NAME                       OPERATION  JOB_MODE  STATE        ATTACHED_SESSIONS
--------------- ------------------------------ ---------- --------- ------------ -----------------
EXPORT          SYS_IMPORT_TABLE_02            IMPORT     TABLE     NOT RUNNING                  0
SYS             M_IMP_COPY_1521305009006       IMPORT     TABLE     NOT RUNNING                  0

Jobs with STATE = NOT RUNNING and ATTACHED_SESSIONS = 0 are orphaned and safe to remove.

💡 On RAC environments, the query is identical. Run it from any node.

Removal

⚠️ Confirm both JOB_NAME and OWNER_NAME before running the DROP. The master table is a real table in the user’s schema.

Drop the master tables for each orphaned job:

DROP TABLE EXPORT.SYS_IMPORT_TABLE_02;
DROP TABLE SYS.M_IMP_COPY_1521305009006;

DROP TABLE EXPORT.SYS_IMPORT_TABLE_02;
DROP TABLE SYS.M_IMP_COPY_1521305009006;

Recyclebin

If the recyclebin parameter is enabled, dropped tables go to the recycle bin instead of being permanently deleted. Check the current setting:

SHOW PARAMETER recyclebin;

SHOW PARAMETER recyclebin;

If the value is ON, explicitly purge each table:

PURGE TABLE EXPORT.SYS_IMPORT_TABLE_02;
PURGE TABLE SYS.M_IMP_COPY_1521305009006;

PURGE TABLE EXPORT.SYS_IMPORT_TABLE_02;
PURGE TABLE SYS.M_IMP_COPY_1521305009006;

⚠️ Without the purge, the jobs continue showing up in DBA_DATAPUMP_JOBS even after the DROP.

Validation

Confirm there are no remaining orphaned jobs:

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

Expected output:

no rows selected

no rows selected

Generating the Commands Automatically

Instead of building the commands manually, use the query below. It generates DROP and PURGE statements only for jobs with STATE = NOT RUNNING and ATTACHED_SESSIONS = 0:

SELECT
    'DROP TABLE ' || owner_name || '.' || job_name || ';' AS cmd_drop,
    'PURGE TABLE ' || owner_name || '.' || job_name || ';' AS cmd_purge
FROM dba_datapump_jobs
WHERE state             = 'NOT RUNNING'
  AND attached_sessions = 0;

SELECT
    'DROP TABLE ' || owner_name || '.' || job_name || ';' AS cmd_drop,
    'PURGE TABLE ' || owner_name || '.' || job_name || ';' AS cmd_purge
FROM dba_datapump_jobs
WHERE state             = 'NOT RUNNING'
  AND attached_sessions = 0;

Copy the output, review the names, and run them in sequence: all DROP statements first, then all PURGE statements.

⚠️ Do not pipe this output directly into EXECUTE IMMEDIATE without reviewing it first. Confirm that no legitimate job appears in the list before proceeding.

Quick Reference

-- 1. Identify orphaned jobs
SELECT owner_name, job_name, state, attached_sessions
FROM dba_datapump_jobs
WHERE state = 'NOT RUNNING';

-- 2. Drop master tables
DROP TABLE <owner>.<job_name>;

-- 3. Purge if recyclebin is ON
PURGE TABLE <owner>.<job_name>;

-- 4. Validate
SELECT * FROM dba_datapump_jobs;

-- 1. Identify orphaned jobs
SELECT owner_name, job_name, state, attached_sessions
FROM dba_datapump_jobs
WHERE state = 'NOT RUNNING';

-- 2. Drop master tables
DROP TABLE <owner>.<job_name>;

-- 3. Purge if recyclebin is ON
PURGE TABLE <owner>.<job_name>;

-- 4. Validate
SELECT * FROM dba_datapump_jobs;