ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated

发表于 2012 年 4 月 26 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

IMPDP导入数据发现ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated错误

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN DBMS_JOB.ISUBMIT( JOB=> 63, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', 
NO_PARSE=> TRUE); END;   
 
Job "GBEAS3"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 16:05:58

错误原因(该job=63已经存在数据库中)

select job, what from   dba_jobs where job=63; 

JOB     WHAT 
-----   -------- 
63      proc_xifenfei

注意:如果该job正在运行,可能需要查询DBA_JOBS_RUNNING

解决办法

1.手工创建job,指定一个不存在的job 号
declare
  m_job number;

begin
  select max (job) + 1 
  into   m_job 
  from   dba_jobs; 

BEGIN DBMS_JOB.ISUBMIT( JOB=> m_job, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', 
NO_PARSE=> TRUE); END; 
end;
/

2.删除原存在job
exec dbms_job.remove (63);

这样的情况，一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致

此条目发表在 ORA-xxxxx 分类目录。将固定链接加入收藏夹。

《ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated》有 1 条评论

  1. 惜分飞 说：
    2012 年 4 月 26 日16:50

    ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated [ID 783299.1]

    Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 20-SEP-2011***

Symptoms
You received the following errors when performing DataPump import:
ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
BEGIN DBMS_JOB.ISUBMIT (JOB => 61, NEXT_DATE => TO_DATE ('2009-02-16 00:00:00',
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL => 'TRUNC (SYSDATE + 1)', 
WHAT => 'Delete_TS_BLOBS;', NO_PARSE => TRUE); END;

Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 1 error(s) at 15:14:38

Cause
The job with ID 61 already exists in the target database. 
Always the job ID (JOBID) must be be unique (two jobs with same ID are not allowed).

Verify the job existence:

select job, what 
from   dba_jobs; 

JOB     WHAT 
-----   -------- 
61      AGILE9_WEEKLY_ANALYZE

NOTE If a job is already running, then it may not show up in view USER_JOBS, 
if another user started the job. You have to check the 
view DBA_JOBS to verify the job ID is not in use. 


Solution
1. Manually create the failed job with a different job ID

For example: 

declare
  m_job number;

begin
  select max (job) + 1 
  into   m_job 
  from   dba_jobs; 

  dbms_job.isubmit (job => m_job, next_date => to_date ('2009-02-16 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),
  interval => 'trunc (sysdate + 1)', what => 'Delete_TS_BLOBS;', no_parse => true); 
end;
/

Or:

2. Remove the existing job with same job ID (61) (only if the job is not required) 
and then perform the import

connect / as sysdba
exec dbms_job.remove (61); 
exit