ORA-31634: job already exists

SYMPTOMS

You are running a DataPump job (export or import) and this fails with the following errors:

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

CHANGES

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

CAUSE

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.

SOLUTION

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 ? 



Step 4. Determine in SQL*Plus the related master tables:

-- locate Data Pump master tables: 

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   593041                 TABLE               SYSTEM.SYS_EXPORT_SCHEMA_97                                                                                                                                     
VALID   593522                 TABLE               SYSTEM.SYS_EXPORT_SCHEMA_98  

Step 5. For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.:

DROP TABLE  SYSTEM.SYS_EXPORT_SCHEMA_98;

-- For systems with recycle bin additionally run:
purge dba_recyclebin;




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.

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值