ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated

联系:手机(+86 13429648788) 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. 惜分飞 说:

    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
    

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>