oracle job的迁移
set serveroutput on size 100000set termout on
set feedback off
clear screen
spool /opt/soft/bak/make_jobs.sql
prompt -- exporting jobs
begin
>
declare
subtype job_type isuser_jobs.JOB%type ;
subtype max_text_typeisvarchar2( 8191 char ) ;
type job_tab_type istable of job_type index by pls_integer ;
type sql_tab_type istable 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=>'01-JAN-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 in1 .. 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
页:
[1]