impdp system/******** directory=DUMP_DIR_TEST dumpfile=czyjpxdb1113.dmp logfile=czyjpxdb1113.log schemas=mgrdb,accdb,txndb,comdb,czyjdb,rpt,stldb remap_tablespace=TBS_CZKDATA:TBS_CZYJDATA1
报错:
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=> 'CZYJDB', PUSER=> 'CZYJDB', CUSER=> 'CZYJDB', NEXT_DATE=> TO_DATE('2014-11-13 22:03:45', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'SYSDATE + 120/1440 ', BROKEN=> FALSE, WHAT=> 'dbms_refresh.refresh(''"CZYJDB"."MV_TBL_CARD_ACCOUNT"'');', NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMER
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 4 error(s) at Fri Nov 14 09:00:27 2014 elapsed 0 00:30:37
报错说明:该job已经存在
select job, what from dba_jobs;
JOB WHAT
----- ----------------------------------------------------------------------------------------------------
4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
4002 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get
_preference('SMTP_HOST_PORT'));
23 dbms_refresh.refresh('"CZYJDB"."MV_TBL_CARD_ACCOUNT"');
发现23号job确实存在
解决方法:
手动创建一个未使用过的job号来使用
declare
my_job number;
begin
select max (job) + 1
into my_job
from dba_jobs;
dbms_job.isubmit (job => my_job, next_date => to_date ('2014-11-15 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), interval => 'trunc (sysdate + 1)', what => 'create_MV_TBL_CARD_ACCOUNT;', no_parse => true);
end;
/
再次执行impdp,成功!
参考文档:
DataPump Import (IMPDP) Errors ORA-39083 ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated (Doc ID 783299.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 28-May-2014***
SYMPTOMS
You receive 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. The job ID (JOBID) must always 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
REFERENCES