-2

I have a working DB holding schema1. I needed to clone the schema1 to schema2. I did this with expdp and impdp. Everything worked fine, least when doing impdp for metadata I got the following error:

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=> 3, LUSER=> 'SCHEMA2', PUSER=> 'SCHEMA2', CUSER=> 'SCHEMA2', NEXT_DATE=> TO_DATE('2017-10-30 11:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'trunc(sysdate,''HH24'')+1/24', BROKEN=> FALSE, WHAT=> 'a_database_job;', NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_N

Now the way I read this is because of the jobs that were trying to get in the SYS.DBMS_IJOB and keep he same number perhaps for the second schema.

So my question is How do I migrate the / a set of jobs from schema1 to schema2 - can this be done by just changing the owner?

hope there is enough data here.

  • You may have some incomplete jobs that became orphans; you might need to look at the dba_scheduler_jobs view to see if something needs cleaning up. Also check permissions on the user ID you are using for the expdp and impdp jobs. – Mark Stewart Nov 6 '17 at 15:06
0

Have a look at DBMS_IJOB.CHANGE_ENV.

PROCEDURE CHANGE_ENV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 LUSER                          VARCHAR2                IN
 PUSER                          VARCHAR2                IN
 CUSER                          VARCHAR2                IN
 NLSENV                         VARCHAR2                IN

Example:

declare
  my_job number;
begin
  dbms_job.submit(job => my_job, 
    what => 'begin null; end;',
    next_date => sysdate+1,
    interval => 'sysdate+1');
end;
/

SQL> select job, log_user, priv_user, schema_user, what from dba_jobs;

       JOB LOG_USER   PRIV_USER  SCHEMA_USER WHAT
---------- ---------- ---------- ----------- --------------------
         1 SYS        SYS        SYS         begin null; end;

begin
  for j in (select * from dba_jobs where job = 1)
  loop
    dbms_ijob.change_env(j.job, 'BP', 'BP', 'BP', j.nls_env);
  end loop;
  commit;
end;
/

select job, log_user, priv_user, schema_user, what from dba_jobs;

       JOB LOG_USER   PRIV_USER  SCHEMA_USER WHAT
---------- ---------- ---------- ----------- --------------------
         1 BP         BP         BP          begin null; end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.