Migration of oracle job

Because the content of JOB is written to death, if you use remap to import to other users, its log_user is still the same, and the id of job is fixed, which is likely to conflict with the current library, so it is recommended to take out the ddl of job.

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 > 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' | | chr (10); job: = instr (sql_tab (x),' (job= >') Sqlx: = sqlx | | substr (sql_tab (x), 1, job-1) | | chr (10); what: = instr (sql_tab (x),', what= >'); sqlx: = sqlx | | substr (sql_tab (x), job, what-job) | | chr (10) Next_date: = instr (sql_tab (x),', next_date= >'); sqlx: = sqlx | | substr (sql_tab (x), what, next_date-what) | | chr (10); interval: = instr (sql_tab (x),', interval= >') -- sqlx: = sqlx | | substr (sql_tab (x), next_date, interval-next_date) | | chr (10); sqlx: = sqlx | | q' |, next_date= > '01Mushani 3000' |' | chr (10); no_parse: = instr (sql_tab (x),', no_parse= >') Sqlx: = sqlx | | substr (sql_tab (x), interval, no_parse-interval) | | chr (10); sqlx: = sqlx | |', no_parse= > TRUE' | | chr (10) | |');'| chr (10); sqlx: = sqlx | 'commit;' | | chr (10) | | chr (10) | Sqlx: = sqlx | | 'end;' | | chr (10) | |' /'| | chr (10); sql_tab (x): = sqlx; end format; begin get_jobs; if job_tab.count > 0 then for i in 1. Job_tab.count loop sql_tab (I): =''; sys.dbms_job.user_export (job = > job_tab (I), mycall = > sql_tab (I)); 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 meet

ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

This means that the job column is repeated, and there are two ways you can do it. One is to reset the job and change it to something that no one uses.

The other is to delete the current 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's probably because your user is not job's owner.

Select job,log_user,priv_user,schema from dba_jobs where job=25

Then switch to the past and delete, in the same way, the establishment must also use the current user.