ANBOB™

数据库的JOB 是存储在job$表中，其中job 列也就是表的唯一约束列，job的编号必须唯一，默认是有SYS.JOBSEQ生成，也可以创建时手动指定，如果job编号违反了唯一约束就无法创建JOB.
比如imp或impdp时的导入Job 时会记录原job ID, 如果job 已存在就会遇到如下错误

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

NOTICE:
这里所说的Job 是dbms_job，而不是dbms_scheduler_job, scheduler job的数据是存在SCHEDULER$_JOB

下面我来演示一下这个问题及解决方法

login anbob user create procedure and job,and try to imp job to anbob1 user;

sys@ANBOB>conn anbob/anbob;
Connected.
anbob@ANBOB>create procedure p1
  2  is
  3  begin
  4  null;
  5  end;
  6  /

anbob@ANBOB>DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( X ,  'anbob.p1;',sysdate,'TRUNC(LAST_DAY(SYSDATE)) + 1',no_parse  => FALSE);
  6  COMMIT;
  7  END;
  8  /

PL/SQL procedure successfully completed.

[oracle@db231 ~]$ expdp system/oracle owner=anbob include=procedure,job directory=datapump dumpfile=anbobjob.dump

sys@ANBOB>create user anbob1 identified by anbob;
User created.

sys@ANBOB>grant create session,create procedure,create job to anbob1;
Grant succeeded.

[oracle@db231 ~]$ impdp system/oracle  directory=datapump dumpfile=anbobjob.dump  remap_schema=anbob:anbob1

Import: Release 11.2.0.3.0 - Production on Wed Sep 25 08:57:26 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=datapump dumpfile=anbobjob.dump remap_schema=anbob:anbob1 
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/JOB
ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN SYS.DBMS_IJOB.SUBMIT( JOB=> 23, LUSER=> 'ANBOB1', PUSER=> 'ANBOB1', CUSER=> 'ANBOB1', NEXT_DATE=> TO_DATE('2013-10-01 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(LAST_DAY(SYSDATE)) + 1', BROKEN=>  FALSE, WHAT=> 'anbob.p1;', NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 08:57:27

sys@ANBOB>select job,what,INTERVAL from dba_jobs where SCHEMA_USER='ANBOB';

                 JOB WHAT                           INTERVAL
-------------------- ------------------------------ ------------------------------
                  23 anbob.p1;                      TRUNC(LAST_DAY(SYSDATE)) + 1

Tip:
可以看到这就是刚才在ANBOB用户下创建的JOB,在ANBOB1用户创建时仍然使用job number 23 所以失败.

解决方法
1. Manually create the failed job with a different job ID
2. Remove the existing job with same job ID and then perform the import

手动创建，如果多个JOB可以用pl/sql 创建

sys@ANBOB>grant dba to anbob1;

sys@ANBOB>conn anbob1
Enter password: 
Connected.

anbob1@ANBOB>declare
  2  cursor c is select job,what,INTERVAL from dba_jobs where SCHEMA_USER='ANBOB';
  3  l_jobx number;
  4  l_what varchar2(30);
  5  begin
  6  select max(job) into l_jobx from dba_jobs;
  7    for rec_c in c loop
  8       l_jobx:=l_jobx+1;
  9       l_what:=replace(upper(rec_c.what),'ANBOB',USER);
 10       dbms_job.isubmit(l_jobx,l_what,sysdate+1/24,rec_c.interval);
 11       commit;
 12       dbms_output.put_line('what:'||l_what);
 13    end loop;
 14  end;
 15  /
what:ANBOB1.P1;

PL/SQL procedure successfully completed.

anbob1@ANBOB>select job,what,next_date,interval from user_jobs;

                 JOB WHAT                           NEXT_DATE INTERVAL
-------------------- ------------------------------ --------- ------------------------------
                4003 ANBOB1.P1;                     25-SEP-13 TRUNC(LAST_DAY(SYSDATE)) + 1

sys@ANBOB>select job,what,interval from dba_jobs;
...
                  23 anbob.p1;                      TRUNC(LAST_DAY(SYSDATE)) + 1
                4003 ANBOB1.P1;                     TRUNC(LAST_DAY(SYSDATE)) + 1

sys@ANBOB>revoke dba from anbob1;
Revoke succeeded.

—
Done.

打赏

微信扫一扫，打赏作者吧～