expdp ORA-31634: job already exists solution

ORA-31634 and ORA-31664 Expdp/Impdp Error Solution

On oracle database server you can take backup using datapump expdp or RMAN. if you configure cronjob or scheduler for expdp daily backup than you may get error like 

ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

CAUSE:
You will get this error because you are not using job_name for datapump job. you are using system generated job_name and in oracle datapump system can generated only up to 99 unique jobs . so in some case job_name already exists or you are running many expdp jobs at same time more than 99 jobs and datapump can not build a unique name in system so you can get this error.

SOLUTION:

System generate unique name with SYS_EXPORT_<mode>_NN (where NN upto 99) so you have to give unique JOB_NAME in data pump jobs.

Example.
expdp system/password directory=DUMP_BACKUP dumpfile=filename.dmp logfile=logfile.log job_name=your_job_name schemas=schema_name 'INCLUDE=TABLE:"IN' '('\''TBLWEBINAUDITLOG'\'')"' compression=all

If you don't want to use JOB_NAME than follow below steps.

STEP1:
# sqlplus / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50

#  locate Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode) "JOB_MODE", state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

OUTPUT:

OWNER_NAME   JOB_NAME                 OPERATION    JOB_MODE      STATE           ATTACHED
----------                -------------------              ---------            ---------          -----------           --------
SCOTT                  EXPDP_46738                      EXPORT     SCHEMA        EXECUTING          1
SCOTT                  SYS_EXPORT_TABLE_01   EXPORT    TABLE             NOT RUNNING     0
SCOTT                  SYS_EXPORT_TABLE_72   EXPORT   TABLE             NOT RUNNING     0
SYSTEM               SYS_EXPORT_FULL_99      EXPORT    FULL               NOT RUNNING     0

STEP2:
Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import.
Data Pump job that has been temporary stopped, but is actually a job that failed. (E.g. the full database export job by SYSTEM is not a job that failed, but was deliberately paused with STOP_JOB).

Find Related Datapump Master Tables using below query.

SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS    OBJECT_ID OBJECT_TYPE OWNER.OBJECT
-------      ----------      ------------           -------------------------
VALID     85283             TABLE             SCOTT.EXPDP_20051121
VALID     85215             TABLE             SCOTT.SYS_EXPORT_TABLE_02
VALID      85162           TABLE              SYSTEM.SYS_EXPORT_FULL_01

STEP3:
Now delete the master tables.

drop table SCOTT.EXPDP_20051121;
drop table  SCOTT.SYS_EXPORT_TABLE_02;
drop table  SYSTEM.SYS_EXPORT_FULL_01;

purge dba_recyclebin;

in-case of you get error while doping table than use ("table_name") below query to drop table.

drop table SCOTT."EXPDP_20051121";

Now re-execute your expdp/impdp datapump jobs.

Give Your Comments if any......!!! Thanks.