expdp fails with error: ORA-31634 and ORA-31664 error

=====================
expdp fails with error: ORA-31634 and ORA-31664 error
=====================

After running export there sis this error:

ORA-31634: job already exists

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

=====================
The Issue
=====================

Per Oracle document #Running DataPump Jobs Results In ORA-31634 ORA-31664 (Doc ID 1384163.1)

Summary: There must be a unique job name to an export job.

By default unique job name is something like SYS_EXPORT_SCHEMA_99 where 99 is a sequence .

The problem, is that after 99 runs, a unique job no longer can be created, since the sequence is limited to 2 digit numbers. (01-99) 

Possible cause for error:

Option A - If you are just running a single job then there are probably old job names that are still found in the database and this is creating the conflict.

Option B - If you are running lots of jobs (more than 99) at a time, then the problem is that DataPump cannot build more than 99 consecutive jobnames when using the default job_name because the default job_name is system-generated in the form SYS_EXPORT_<mode>_NN, where NN expands to a 2-digit incrementing integer starting at 01, therefore the largest value for NN would be 99.

The Solution:

Option A - 

Run expdb with JOB_NAME=unique_job_name parameter

For example:

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=my_job_11111 NOLOGFILE=YES

Option B - 

Clean the old jobs following procedure from oracle Technote 336014.1  - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS  (Doc ID 336014.1)

From Oracle Database Utilities guide:

JOB_NAME

Default: system-generated name of the form SYS_EXPORT_<mode>_NN

Purpose

Used to identify the export job in subsequent actions, such as when the ATTACH parameter is used to attach to a job, or to identify the job using the DBA_DATAPUMP_JOBS or USER_DATAPUMP_JOBS views.

Syntax and Description

JOB_NAME=jobname_string

The jobname_string specifies a name of up to 30 bytes for this export job. The bytes must represent printable characters and spaces. If spaces are included, then the name must be enclosed in single quotation marks (for example, 'Thursday Export'). The job name is implicitly qualified by the schema of the user performing the export operation. The job name is used as the name of the master table, which controls the export job.

The default job name is system-generated in the form SYS_EXPORT_<mode>_NN, where NN expands to a 2-digit incrementing integer starting at 01. An example of a default name is 'SYS_EXPORT_TABLESPACE_02'.

Example

The following example shows an export operation that is assigned a job name of exp_job:

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=exp_job NOLOGFILE=YES

How to clean Orphaned DataPump Jobs  (Doc ID 336014.1):

COL STATUS FOR A10

COL OBJECT_TYPE FOR A30

COL OBJECT FOR A50

SET LINESIZE 120

SET PAGESIZE 1000

SELECT o.status, o.object_id, o.object_type, 

       o.owner||'.'||object_name 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       27383 TABLE         MY_USER.SYS_EXPORT_SCHEMA_01

VALID       27451 TABLE         MY_USER.SYS_EXPORT_SCHEMA_02

VALID       27440 TABLE         MY_USER.SYS_EXPORT_SCHEMA_03

VALID       27546 TABLE         MY_USER.SYS_EXPORT_SCHEMA_04

VALID       27405 TABLE         MY_USER.SYS_EXPORT_SCHEMA_05

VALID       27367 TABLE         MY_USER.SYS_EXPORT_SCHEMA_06

VALID       27501 TABLE         MY_USER.SYS_EXPORT_SCHEMA_07

...

...  

   

SET PAGESIZE 1000

SET LINESIZE 140

COL drop_command FOR A80

SELECT 'DROP TABLE '||O.owner||'.'||O.object_name||';' as drop_command

  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$%'

   AND J.state='NOT RUNNING'

ORDER BY 1;

DROP_COMMAND

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

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_01;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_02;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_03;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_04;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_05;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_06;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_07;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_08;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_09;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_10;

...

...

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_90;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_91;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_92;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_93;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_94;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_95;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_96;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_97;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_98;

DROP TABLE MY_USER.SYS_EXPORT_SCHEMA_99;

Run the DROP commands + Purge the recycle bin

PURGE DBA_RECYCLEBIN;