链接:http://www.xifenfei.com/2999.html
标题: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号已经存在导致