BLOG DBA TIPS AND TRICKS

Resolving ORA-31634 ORA-31664 EXPDP error

This is how to fix expdp ORA-31634 ORA-31664 error when performing backup with expdp like example below:
$ expdp exporter/exporter dumpfile=expfull_%U.dmp full=y logfile=expfull.log directory=export parallel=4
Export: Release 12.1.0.2.0 – Production on Mon Mar 18 16:00:22 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted $

Cause:
If the entries in DBA_DATAPUMP_JOBS table become equals to 99 for any particular schema then “ORA-31634 : job already exists”.

Solution:
Remove all “orphaned” datapump jobs.

1) Determine the datapump jobs which exist in the database and the status as NOT RUNNING.
 SQL> set line 300
SQL> col OWNER_NAME for a10
SQL> col JOB_NAME for a30
SQL> col OPERATION for a30
col JOB_MODE for a20
SQL> set pagesize 200
SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs where owner_name=’EXPORTER’ and state=’NOT RUNNING’ ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
———- —————————— —————————— ——————– —————————— —————–
EXPORTER SYS_EXPORT_FULL_81 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_82 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_83 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_84 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_85 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_86 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_87 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_88 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_94 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_95 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_96 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_97 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_98 EXPORT FULL NOT RUNNING 0
EXPORTER SYS_EXPORT_FULL_99 EXPORT FULL NOT RUNNING 0

14 rows selected.
SQL>

2) Determine the master tables associated to these jobs
 SQL> col OWNER.OBJECT for a50

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 o.owner=’EXPORTER’ and j.state=’NOT RUNNING’ ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT CREATED STATE
——- ———- ———————– ————————————————– ——————- ——————————
VALID 3421470 TABLE EXPORTER.SYS_EXPORT_FULL_81 2018-12-19 01:00:12 NOT RUNNING
VALID 3440771 TABLE EXPORTER.SYS_EXPORT_FULL_82 2018-12-24 01:00:10 NOT RUNNING
VALID 3444617 TABLE EXPORTER.SYS_EXPORT_FULL_83 2018-12-25 01:00:06 NOT RUNNING
VALID 3463941 TABLE EXPORTER.SYS_EXPORT_FULL_84 2018-12-30 01:00:08 NOT RUNNING
VALID 3467816 TABLE EXPORTER.SYS_EXPORT_FULL_85 2018-12-31 01:00:11 NOT RUNNING
VALID 3498676 TABLE EXPORTER.SYS_EXPORT_FULL_86 2019-01-08 01:00:07 NOT RUNNING
VALID 3502509 TABLE EXPORTER.SYS_EXPORT_FULL_87 2019-01-09 01:00:07 NOT RUNNING
VALID 3510208 TABLE EXPORTER.SYS_EXPORT_FULL_88 2019-01-11 01:00:07 NOT RUNNING
VALID 3543247 TABLE EXPORTER.SYS_EXPORT_FULL_94 2019-01-20 01:00:12 NOT RUNNING
VALID 3547107 TABLE EXPORTER.SYS_EXPORT_FULL_95 2019-01-21 01:00:14 NOT RUNNING
VALID 3562527 TABLE EXPORTER.SYS_EXPORT_FULL_96 2019-01-25 01:00:13 NOT RUNNING
VALID 3566384 TABLE EXPORTER.SYS_EXPORT_FULL_97 2019-01-26 01:00:13 NOT RUNNING
VALID 3585746 TABLE EXPORTER.SYS_EXPORT_FULL_98 2019-01-31 01:00:13 NOT RUNNING
VALID 3597354 TABLE EXPORTER.SYS_EXPORT_FULL_99 2019-02-03 01:00:10 NOT RUNNING

14 rows selected.

SQL>

3) Generate script to Drop the master tables:

 SQL> set line 300
SQL> col OWNER_NAME for a10
SQL> col JOB_NAME for a30
SQL> col OPERATION for a30


SELECT ‘drop table ‘|| o.owner||’.’||object_name ||’;’ FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name and o.owner=’EXPORTER’ and j.state=’NOT RUNNING’;

‘DROPTABLE’||O.OWNER||’.’||OBJECT_NAME||’;’
—————————————————
drop table EXPORTER.SYS_EXPORT_FULL_96;
drop table EXPORTER.SYS_EXPORT_FULL_88;
drop table EXPORTER.SYS_EXPORT_FULL_97;
drop table EXPORTER.SYS_EXPORT_FULL_98;
drop table EXPORTER.SYS_EXPORT_FULL_86;
drop table EXPORTER.SYS_EXPORT_FULL_81;
drop table EXPORTER.SYS_EXPORT_FULL_84;
drop table EXPORTER.SYS_EXPORT_FULL_95;
drop table EXPORTER.SYS_EXPORT_FULL_94;
drop table EXPORTER.SYS_EXPORT_FULL_99;
drop table EXPORTER.SYS_EXPORT_FULL_83;
drop table EXPORTER.SYS_EXPORT_FULL_87;
drop table EXPORTER.SYS_EXPORT_FULL_82;
drop table EXPORTER.SYS_EXPORT_FULL_85;

14 rows selected.

SQL>
 4) Execute Drop commands to remove master tables above and re-run the expdp command. This should run fine.