Migration of Oracle Job

Because the job content is written dead, if the use of remap imported to another user, its log_user, and so on, and the job ID is fixed, and the current library is likely to conflict, it is recommended to remove the job's DDL.

DBMS_METADATA.GET_DDL is not allowed. No, you'll find out if you try.

So I wrote a plsql.

Set serveroutput on size 100000set termout onset feedback offclear  screenspool /opt/soft/bak/make_jobs.sqlprompt -- exporting jobsbegin<<  export_jobs >>declare   subtype   job_type        is  user_jobs. job%type    ;  subtype   max_text_type  is   VARCHAR2 ( 8191 char )  ;  type      job_tab_type    is  table of   job_type         index by pls_integer ;  type      sql_tab_ type   is  table of   max_text_type   index  By pls_integer ;        job_tab     job_tab_type  ;  sql_tab     sql_tab_type   ;  job         pls_integer   ;   what        pls_integer   ;  next _date   pls_integer   ;  interval    pls_integer    ;  no_parse    pls_integer   ;   procedure         get_jobs  is  begin         select j.JOB           bulk collect          into job_tab           from user_jobs j          ordEr by 1        ;  end   get_jobs   ;    procedure         format (  x pls_integer )   is         sqlx      max_text_type  :=  null ;  begin                  sqlx :=  ' Begin '                                                        | | &NBSP;CHR (Ten);        job           :=   instr ( sql_tab (x),  ' (job=> '  )  ;         sqlx := sqlx | |   substr ( sql_tab (x), 1, job-1 )                                | | &NBSP;CHR (Ten)  ;        what          :=   instr ( sql_tab (x), ',what=> '  )  ;         sqlx := sqlx | |   substr ( sql_tab (x), job, what-job )                          | | &NBSP;CHR (Ten)  ;        next_date    :=    instr ( sql_tAB (x), ',next_date=> '  ) &NBSP;;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SQLX&NBSP;:=&NBSP;SQLX  | |   substr ( sql_tab (x), what, next_date-what )                   | | &NBSP;CHR (Ten)  ;        interval     :=    instr ( sql_tab (x), ',interval=> '  )  ;    --   sqlx := sqlx | |   substr ( sql_tab (x), next_date, interval-next_date )          | | &NBSP;CHR ( ;        sqlx := sqlx |) |   q ' |,next_date=> ' 01-jan-3000 ' | '                               | |  chR (Ten)  ;        no_parse     :=    instr ( sql_tab (x), ',no_parse=> '  )  ;         sqlx := sqlx | |   substr ( sql_tab (x), interval, no_parse-interval )            | | &NBSP;CHR ( ;        sqlx := sqlx |) |    ', No_parse=>true '                  | | &NBSP;CHR ( |) |   '); '  | | &NBSP;CHR ( ;        sqlx := sqlx |) |    ' commit; '                          | | &NBSP;CHR (         |) |  cHR ( ;        sqlx := sqlx |) |    ' End; '                             | | &NBSP;CHR ( |) |   '/'   | | &NBSP;CHR (Ten)  ;                 sql_tab (x)    :=  sqlx;  end   format  ;             begin       get_jobs;      if                 job_tab.count > 0        then            for           &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;I&NBSP;&NBSP;&NBSP;IN&NBSP;&NBSP;1&NBSP, ....  job_tab.count            loop                   sql_tab (i)  :=  '   ';                   sys.dbms_job.user_export                    (  job    =>  job_ tab (i)                     , mycall =>  sql_tab (i)                    );              &nBsp;    format (i)  ;                   dbms_output.put_line ( sql_tab (i)  )  ;             end   loop             ;      else             dbms_output.put_line (  '-- nothing to  do. '  )  ;       end   if       ;end export_jobs;end;/spool off

Then, use this to get the output to rebuild the job. If you encounter

Ora-00001:unique constraint (SYS. I_job_job) violated

This means that the job column repeats, and you have two methods, one for resetting the job, and the other for no one.

The other is to delete the present job reconstruction.

The delete syntax is

EXEC Dbms_job.remove (25);

If you encounter the following when deleting:

Ora-23421:job number 387 is not a job in the job queue

It is likely that your user is not the owner of the job.

Select Job,log_user,priv_user,schema from Dba_jobs where job=25;

Then switch past and then delete, similarly, the establishment must also use the current user.

This article is from the "Mobile Fighter Gundam Oracle" blog, so be sure to keep this source http://gundam.blog.51cto.com/1845787/1613250

Migration of Oracle Job