联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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-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