--有些时时候需要提交别人你写的job脚本,用下面的存储就可以在命令窗口模式下输出全部的job代码;
set serveroutput on size 1000000;
create or replace procedure proc_generate_job_create_sql is
begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示输出buffer不受限制
sys.dbms_output.put_line('declare');
sys.dbms_output.put_line(' v_job number;');
sys.dbms_output.put_line('begin');
for j in (select what, interval from user_jobs) loop
sys.dbms_output.put_line(' sys.dbms_job.submit(job => v_job,');
sys.dbms_output.put_line(' what => ''' ||
j.what || ''',');
sys.dbms_output.put_line(' next_date => ' ||
j.interval || ',');
sys.dbms_output.put_line(' interval => ''' ||
j.interval || ''');');
end loop;
sys.dbms_output.put_line(' commit;');
sys.dbms_output.put_line('end;');
end;
--使用schedule_job可以更好管理你的job,可以自定义命名你的job.
begin
dbms_scheduler.create_schedule(schedule_name => 'SCHEDULE_自定义名称',
repeat_interval => 'FREQ=DAILY; INTERVAL=1;BYHOUR=0;ByMinute=10',
comments => '每天0点10分执行调度');
dbms_scheduler.create_program(program_name => 'PROGRAM_自定义名称',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN 业务代码(一般为存储过程); END;',
number_of_arguments => 0,
enabled => TRUE,
comments => '描述');
dbms_scheduler.create_job(job_name => 'JOB_自定义名称',
program_name => 'PROGRAM_自定义名称',
schedule_name => 'SCHEDULE_自定义名称',
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => true,
comments => '描述');
--开始运行
dbms_scheduler.run_job(job_name => 'JOB_自定义名称', use_current_session => false);
end;