ORA-31634: job already exists | Datapump Master Table exists

ORA-31634: job already exists

Datapump Master Table exists

Error

Export Datapump job experience below error during export

Export: Release 11.2.0.1.0 - Production on Fri Nov 21 16:09:37 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORA-31634: job already exists

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT", line 1016

ORA-31637: cannot create job EXPDP_BACKUP for user ORACLE

Check the running jobs

SQL> set lines 200 pages 200 numwidth 10

col program for a40

col username for a14

col spid for a10

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,

s.status, s.username, d.job_name, p.spid, s.serial#, p.pid

from v$session s, v$process p, dba_datapump_sessions d

where p.addr=s.paddr and s.saddr=d.saddr;

no rows selected

SQL> COL OPERATION FOR A14

SQL> COL JOB_MODE FOR A14

SQL> COL OWNER_NAME FOR A14

SQL> COL STATE FOR A15

SQL> SET LIN200 PAGES 200

SQL> SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs;

OWNER_NAME     JOB_NAME                       OPERATION      JOB_MODE       STATE           ATTACHED_SESSIONS

-------------- ------------------------------ -------------- -------------- --------------- -----------------

ORACLE         BIN$JWB+GHK0NnLgUKeTGxwsxA==$0 EXPORT         FULL           NOT RUNNING                     0

ORACLE         BIN$JOCfGMFgf4XgUKeTGxwcEA==$0 EXPORT         FULL           NOT RUNNING                     0

ORACLE         EXPDP_BACKUP                   EXPORT         FULL           NOT RUNNING                     0

ORACLE         BIN$JYNqus85nL3gUKeTGxwpmQ==$0 EXPORT         FULL           NOT RUNNING                     0

ORACLE         BIN$JNMboNajQ6DgUKeTGxxb8g==$0 EXPORT         FULL           NOT RUNNING                     0

ORACLE         BIN$JSAQ5vj9TtngUKeTGxw+jg==$0 EXPORT         FULL           NOT RUNNING                     0

6 rows selected.

Checking Orphaned jobs

SQL> SELECT o.status, o.object_id, o.object_type,

o.owner||'.'||object_name "OWNER.OBJECT",o.created,j.state

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name

and j.state='NOT RUNNING' ORDER BY 4,2;

SQL> COL OWNER.OBJECT FOR A20

SQL> /

STATUS  OBJECT_ID OBJECT_TYPE         OWNER.OBJECT         CREATED            STATE

------- --------- ------------------- -------------------- ------------------ ---------------

VALID     1842419 TABLE               ORACLE.EXPDP_BACKUP  21-NOV-15 10.44.50 NOT RUNNING

Clear the master tables created by orphaned jobs

SQL> DROP TABLE ORACLE.EXPDP_BACKUP ;

Table dropped.

Now you can start the export.