使用Oracle调度程序自动完成任务

• 1. 创建作业、计划和时间表
• 2. 创建轻量级作业
• 3. 使用作业链执行一系列相关任务
• 4. 创建窗口和作业类
• 5. 使用高级调度程序概念确定作业优先顺序
• Reference

实验演示准备：

--业务用户jingyu创建测试表t_times
conn jingyu/jingyu
create table t_times(col1 date);
create table t_sum(col1 date, col2 number);
create table t_times_old as select * from t_times where 1=2;
--查看当前t_times表的记录数
select count(*) from t_times;
--查看t_sum表的记录信息
select * from t_sum;

--查看调度相关参数和视图：
show parameter JOB_QUEUE_PROCESSES
show parameter RESOURCE_MANAGER_PLAN

set linesize 140 pagesize 30
col state for a15
col job_name for a30
col owner for a15
col job_style for a30
col job_type for a30
--查询作业
select OWNER, JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from DBA_SCHEDULER_JOBS;
col program_name for a30
col program_type for a30
--查询程序计划
select OWNER, PROGRAM_NAME, PROGRAM_TYPE, ENABLED, MAX_RUNS from DBA_SCHEDULER_PROGRAMS;

1. 创建作业、计划和时间表

## 1.1 作业 ##

--CREATE_JOB（创建作业）
--作业功能：每秒向T_TIMES表插入当前系统时间，运行1w次后终止。
BEGIN
sys.dbms_scheduler.create_job( 
job_name => '"JINGYU"."J_INSERT"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
  -- Insert PL/SQL code here
  insert into t_times values(sysdate);
  commit;
end;',
repeat_interval => 'FREQ=SECONDLY',
start_date => to_timestamp_tz('2015-12-09 05:05:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Insert the current date into the T_TIMES table.',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"JINGYU"."J_INSERT"', attribute => 'max_runs', value => 10000); 
END;
/

--ENABLE(启动作业)
exec dbms_scheduler.enable('"JINGYU"."J_INSERT"');
--DISABLE(禁用作业)
exec dbms_scheduler.disable('"JINGYU"."J_INSERT"');
--DROP_JOB（删除作业）
exec dbms_scheduler.drop_job('"J_INSERT"');

1.2 调度程序计划

--CREATE_PROGRAM（创建调度程序计划）
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"JINGYU"."P_SAVEDATE"',
program_action=>'declare
  v_sum number;
begin
  -- Insert PL/SQL code here
  select count(1) into v_sum from t_times;
  insert into t_sum values(sysdate,v_sum);
  commit;
end;',
program_type=>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'Insert the current date and the count of T_TIMES into the T_SUM.',
enabled=>FALSE);
END;
/

--启用程序计划
exec dbms_scheduler.enable('"JINGYU"."P_SAVEDATE"');
--停用程序计划
exec dbms_scheduler.disable('"JINGYU"."P_SAVEDATE"');
--删除程序计划
exec dbms_scheduler.drop_program('"JINGYU"."P_SAVEDATE"');

注意：

• 计划与作业共享相同的名称空间；后面的时间表和窗口亦是如此。
• 一个时间表可以应用于许多作业；一个计划可以被许多作业调用。

1.3 时间表

--CREATE_SCHEDULE(创建时间表)
BEGIN
sys.dbms_scheduler.create_schedule( 
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone 'Asia/Shanghai',
comments => 'Time: 1/min',
schedule_name => '"JINGYU"."S_PER_MINUTE"');
END;
/

2. 创建轻量级作业

创建并运行轻量级作业

--依据1.2中程序计划(必须已启用)为模板，创建轻量级作业
begin
  dbms_scheduler.create_job(
    job_name => 'lightweight_job_1',
	program_name => 'JINGYU.P_SAVEDATE',
	job_style => 'LIGHTWEIGHT',
	comments => 'summarize the count of t_times now!'
  );
end;
/

--运行轻量级作业
exec dbms_scheduler.run_job('lightweight_job_1');

--连续运行100次轻量级作业
begin
  for i in 1..100
   loop
    dbms_scheduler.run_job('lightweight_job_1');
   end loop;
end;  
/

--删除轻量级作业
exec dbms_scheduler.drop_job('lightweight_job_1');

3. 使用作业链执行一系列相关任务

--CREATE_PROGRAM（先创建调度程序计划P_INSERT）
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"JINGYU"."P_INSERT"',
program_action=>'begin
  -- Insert PL/SQL code here
  insert into t_times_old select * from t_times;
  commit;
  execute immediate ''truncate table t_times'';
end;',
program_type=>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'Insert into t_times_old from t_times and truncate table t_times.',
enabled=>TRUE);
END;
/

--删除PROGRAM
exec DBMS_SCHEDULER.DROP_PROGRAM('P_INSERT');

3.1 创建链对象，定义链步骤，定义链规则

BEGIN
  --创建链
  sys.dbms_scheduler.create_chain( 
  comments => 'savedata to t_sum and insert data into t_times_old',
  chain_name => '"JINGYU"."CHAIN1"');
  --定义链步骤S1
  sys.dbms_scheduler.define_chain_step( 
  chain_name => '"JINGYU"."CHAIN1"',
  step_name => '"S1"',
  program_name => '"JINGYU"."P_SAVEDATE"');
  --定义链步骤S2
  sys.dbms_scheduler.define_chain_step( 
  chain_name => '"JINGYU"."CHAIN1"',
  step_name => '"S2"',
  program_name => '"JINGYU"."P_INSERT"');
  --定义链规则C1_RULE1
  sys.dbms_scheduler.define_chain_rule( 
  chain_name => '"JINGYU"."CHAIN1"',
  condition => 'TRUE',
  rule_name => 'C1_RULE1',
  action => 'START S1',
  comments => 'start the chain');
  --定义链规则C1_RULE2
  sys.dbms_scheduler.define_chain_rule( 
  chain_name => '"JINGYU"."CHAIN1"',
  condition => 'S1 COMPLETED',
  rule_name => 'C1_RULE2',
  action => 'START S2');
END;
/

--删除链
exec dbms_scheduler.drop_chain(chain_name => '"JINGYU"."CHAIN1"');
--强制删除链
BEGIN
DBMS_SCHEDULER.DROP_CHAIN (
   chain_name   => '"JINGYU"."CHAIN1"',
   force        => TRUE);
END;
/

3.2 启用、运行、监控链

--启用链
exec dbms_scheduler.enable('CHAIN1');

--先验证当前数据情况
select * from t_sum order by col1;
select count(1) from t_times_old;
select count(1) from t_times;

--立即运行链，从S1开始
BEGIN
dbms_scheduler.run_chain(
chain_name => 'CHAIN1',
job_name => 'J_Chain1_S1_now',
start_steps => 'S1');
END;
/
--立即运行链，指定从S2开始
BEGIN
dbms_scheduler.run_chain(
chain_name => 'CHAIN1',
job_name => 'J_Chain1_S2_now',
start_steps => 'S2');
END;
/
--结果：从S1开始，根据定义的规则，S1完成后运行S2; 而指定从S2开始就不会运行S1.
--停止上面两个JOB（这两个停止了默认就drop了）
exec DBMS_SCHEDULER.STOP_JOB('J_CHAIN1_S1_NOW');
exec DBMS_SCHEDULER.STOP_JOB('J_Chain1_S2_now');

--创建定时运行链CHAIN1的作业J_chain1
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
  job_name        => 'J_chain1',
  job_type        => 'CHAIN',
  job_action      => 'CHAIN1',
  repeat_interval => 'freq=daily;byhour=14;byminute=0;bysecond=0',
  comments        => 'A job to run chain1',
  enabled         => TRUE);
END;
/

--停止JOB（停止会恢复JOB调度状态）
exec DBMS_SCHEDULER.STOP_JOB('J_CHAIN1');
--删除JOB
exec DBMS_SCHEDULER.DROP_JOB('J_CHAIN1');

--监视作业链
select owner, chain_name, enabled, comments from dba_scheduler_chains;

4. 创建窗口和作业类

--创建窗口
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"NOON_WINDOW"',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
start_date=>to_timestamp_tz('2015-12-22 Asia/Shanghai', 'YYYY-MM-DD TZR'),
duration=>numtodsinterval(60, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
end_date=>null,
window_priority=>'LOW',
comments=>'Task running over the lunch hour every day.');
END;
/

--创建作业类
BEGIN
sys.dbms_scheduler.create_job_class( 
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
log_history => 60,
resource_consumer_group => 'LOW_GROUP',
comments => 'Jobs for test',
job_class_name => '"TEST JOBS"');
END;
/

5. 使用高级调度程序概念确定作业优先顺序

## 5.1 使用作业类 ##

--作业"J_INSERT"添加到作业类"TEST JOBS"
exec dbms_scheduler.set_attribute(name=>'J_INSERT', attribute=>'job_class', value=>'"TEST JOBS"');

--设置作业"J_INSERT"优先级为1
exec dbms_scheduler.set_attribute(name=>'J_INSERT', attribute=>'job_priority', value=>1);
--注：作业优先级的值1~5，1的优先级最高。

--作业类可以控制日志记录级别
--查询DBA_SCHEDULER_JOB_LOG视图
select * from DBA_SCHEDULER_JOB_LOG;
select job_name, log_date, status from DBA_SCHEDULER_JOB_LOG;
--查询DBA_SCHEDULER_JOB_RUN_DETAILS视图
select * from DBA_SCHEDULER_JOB_RUN_DETAILS;

5.2 使用窗口

如果关闭窗口时，窗口中尚存在正在运行的作业，默认作业会继续运行完。

--查询作业信息
set linesize 200
col owner for a30
col job_name for a35
select owner, job_name, stop_on_window_close, job_priority from dba_scheduler_jobs;

--设置stop_on_window_close属性(This attribute only applies if the schedule of a job is a window or a window group.)
--a.先把作业"J_INSERT"设置为窗口"NOON_WINDOW"
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"JINGYU"."J_INSERT"', attribute => 'start_date'); 
sys.dbms_scheduler.set_attribute_null( name => '"JINGYU"."J_INSERT"', attribute => 'repeat_interval'); 
sys.dbms_scheduler.set_attribute_null( name => '"JINGYU"."J_INSERT"', attribute => 'end_date'); 
sys.dbms_scheduler.set_attribute( name => '"JINGYU"."J_INSERT"', attribute => 'schedule_name', value => '"SYS"."NOON_WINDOW"'); 
END;
/
--b.设置作业"J_INSERT"的属性stop_on_window_close
BEGIN
sys.dbms_scheduler.disable( '"JINGYU"."J_INSERT"' ); 
sys.dbms_scheduler.set_attribute( name => '"JINGYU"."J_INSERT"', attribute => 'stop_on_window_close', value => TRUE); 
sys.dbms_scheduler.enable( '"JINGYU"."J_INSERT"' ); 
END;
/

Reference

- OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.