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

I got ” ORA-31664: unable to construct unique job name when defaulted | ORA-31634: job already exists ” error in the Oracle database.

 

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

Details of error are as follows.

Export: Release 19.0.0.0.0 - Production on Fri Jun 11 16:47:33 2021
Version 19.8.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

 

 

 

ORA-31634: job already exists

This ORA-31664 and ORA-31634 errors are related with the unique job name when defaulted or job name already exists.

This can occur when either running a single DataPump jobs or when you are executing multiple jobs at the same time.

This can happen when you do not specify a unique JOB_NAME for the DataPump job (using a default name) and for some reason that JOB_NAME already exists, or if you are running many jobs at the same time (more than 99 jobs) and DataPump cannot build a unique name.

 

To solve this error, drop table of the previous export job names table as follows.

SELECT 'DROP table ' || owner_name || '.' || job_name || purge';' FROM DBA_DATAPUMP_JOBS WHERE STATE = 'NOT RUNNING';

 

run the following drop table commands.

DROP table SYS.SYS_EXPORT_SCHEMA_87;
DROP table SYS.SYS_EXPORT_SCHEMA_12;
DROP table SYS.SYS_EXPORT_SCHEMA_78;
DROP table SYS.SYS_EXPORT_SCHEMA_08;
DROP table SYS.SYS_EXPORT_SCHEMA_21;

 

Then try to run export again.

OR

You can add the job_name parameter into export command as follows.

job_name=MSDB_EXPORT


nohup expdp \"/ as sysdba\" directory=datapump_dir dumpfile=MSDB_SCHEMA%U.dmp schemas=MSDB logfile=MSDB_SCHEMA.log parallel=96 compression=all exclude=statistics job_name=MSDB_EXPORT &

 

 

1. 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. You could try to remove these job using the steps in the following
Note 336014.1 – How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

2. 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.

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

To avoid this issue, please use the job_name DataPump parameter to create unique job names that will not conflict with any other job names in the database instead of using the default job_name.

 

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 1,790 views last month,  8 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply