18 - Oracle Scheduler调度

第十八课

18、Oracle Scheduler调度

 作业job
 程序program
 调度schedule
 链chain
 窗口window
 作业类
CJQ0进程:作业队列协调器
Jnnn进程:运行作业
参数:job_queue_processes
创建作业:

18.1 调度schedule

–基于时间的调度

BEGIN
  sys.dbms_scheduler.create_job(job_name => '"OUZY"."JOB1"'
                               ,job_type => 'PLSQL_BLOCK'
                               ,job_action => 'insert into t2 values(sysdate);'
                               ,repeat_interval => 'FREQ=SECONDLY;INTERVAL=10'
                               ,start_date => systimestamp at TIME ZONE
                                              'Asia/Shanghai'
                               ,job_class => '"DEFAULT_JOB_CLASS"'
                               ,auto_drop => FALSE
                               ,enabled => TRUE);
END;

作业的命令类型:
程序、匿名PL/SQL块、存储过程、可执行文件、链

创建程序program:

–创建程序program1

BEGIN
  dbms_scheduler.create_program(program_name => '"OUZY"."PROGRAM1"'
                               ,program_action => 'insert into tbs(name,dt) values(''program1'',sysdate);'
                               ,program_type => 'PLSQL_BLOCK'
                               ,enabled => TRUE);

从作业中抽取作业内容，就可以在多个作业中调用，而不必重复定义。

创建调度:

BEGIN
  dbms_scheduler.create_schedule(repeat_interval => 'FREQ=SECONDLY;INTERVAL=10'
                                ,start_date => systimestamp at TIME ZONE
                                               'Asia/Shanghai'
                                ,schedule_name => '"OUZY"."SCHEDULE1"');
END;

创建一个job(使用程序和调度):

BEGIN
  dbms_scheduler.create_job(job_name => '"OUZY"."JOB2"'
                           ,program_name => '"OUZY"."PROGRAM1"'
                           ,schedule_name => '"OUZY"."SCHEDULE1"'
                           ,job_class => '"DEFAULT_JOB_CLASS"'
                           ,auto_drop => TRUE
                           ,enabled => TRUE);
END;

18.2 作业链

链是为达到组合目标而链接在一起的一系列已命名的程序。
运行程序A 然后运行程序B，如果程序A 和程序B 成功完成，则只运行程序C，否则运行程序D。
要启动一个链，必须创建一个作业。

–创建测试表
create table tbs(name varchar2(20),dt date);

–创建程序program1

BEGIN
  dbms_scheduler.create_program(program_name => '"OUZY"."PROGRAM1"'
                               ,program_action => 'insert into tbs(name,dt) values(''program1'',sysdate);'
                               ,program_type => 'PLSQL_BLOCK'
                               ,enabled => TRUE);
END;

–创建程序program2

BEGIN
  dbms_scheduler.create_program(program_name => '"OUZY"."PROGRAM2"'
                               ,program_action => 'insert into tbs(name,dt) values(''program2'',sysdate);'
                               ,program_type => 'PLSQL_BLOCK'
                               ,enabled => TRUE);
END;

–创建程序program3

BEGIN
  dbms_scheduler.create_program(program_name => '"OUZY"."PROGRAM3"'
                               ,program_action => 'insert into tbs(name,dt) values(''program3'',sysdate);'
                               ,program_type => 'PLSQL_BLOCK'
                               ,number_of_arguments => 0
                               ,comments => ''
                               ,enabled => TRUE);
END;

–创建作业链

BEGIN
  dbms_scheduler.create_chain(chain_name => '"OUZY"."CHAIN1"');
  dbms_scheduler.define_chain_step(chain_name => '"OUZY"."CHAIN1"'
                                  ,step_name => '"STEP1"'
                                  ,program_name => '"OUZY"."PROGRAM1"');
  dbms_scheduler.define_chain_step(chain_name => '"OUZY"."CHAIN1"'
                                  ,step_name => '"STEP2"'
                                  ,program_name => '"OUZY"."PROGRAM2"');
  dbms_scheduler.define_chain_step(chain_name => '"OUZY"."CHAIN1"'
                                  ,step_name => '"STEP3"'
                                  ,program_name => '"OUZY"."PROGRAM3"');
    dbms_scheduler.define_chain_rule(chain_name => '"OUZY"."CHAIN1"'
                                  ,condition => 'TRUE'
                                  ,rule_name => 'RULE001'
                                  ,action => 'start step1');
  dbms_scheduler.define_chain_rule(chain_name => '"OUZY"."CHAIN1"'
                                  ,condition => 'step1 succeeded'
                                  ,rule_name => 'RULE002'
                                  ,action => 'start step2');
  dbms_scheduler.define_chain_rule(chain_name => '"OUZY"."CHAIN1"'
                                  ,condition => 'step1 failed'
                                  ,rule_name => 'RULE003'
                                  ,action => 'start step3');
  dbms_scheduler.define_chain_rule(chain_name => '"OUZY"."CHAIN1"'
                                  ,condition => 'step2 completed'
                                  ,rule_name => 'RULE004'
                                  ,action => 'END');
  dbms_scheduler.define_chain_rule(chain_name => '"OUZY"."CHAIN1"'
                                  ,condition => 'step3 completed'
                                  ,rule_name => 'RULE005'
                                  ,action => 'END');
  dbms_scheduler.enable('"OUZY"."CHAIN1"');
END;

–创建job

BEGIN
  dbms_scheduler.create_job(job_name => '"OUZY"."JOB1"'
                           ,job_type => 'CHAIN'
                           ,job_action => '"OUZY"."CHAIN1"'
                           ,repeat_interval => 'FREQ=SECONDLY;INTERVAL=10'
                           ,start_date => systimestamp at TIME ZONE
                                          'Asia/Shanghai'
                           ,job_class => '"DEFAULT_JOB_CLASS"'
                           ,auto_drop => TRUE
                           ,enabled => TRUE);
END;

18.3 窗口

窗口和资源计划相关联，都到窗口时间，特定资源计划被激活，可以控制job的资源使用。
尽量避免窗口重叠。

BEGIN
  dbms_scheduler.create_window(window_name => '"WINDOW_TEST"'
                              ,resource_plan => 'DEFAULT_PLAN'
                              ,start_date => systimestamp at TIME ZONE
                                             'Asia/Shanghai'
                              ,duration => numtodsinterval(240, 'minute')
                              ,repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=30;BYSECOND=30'
                              ,end_date => NULL
                              ,window_priority => 'LOW'
                              ,comments => '');
END;

BEGIN
  dbms_scheduler.create_job(job_name => '"OUZY"."JOB1"'
                           ,program_name => '"OUZY"."PROGRAM1"'
                           ,schedule_name => '"SYS"."WINDOW_TEST"'
                           ,job_class => '"DEFAULT_JOB_CLASS"'
                           ,auto_drop => TRUE
                           ,enabled => FALSE);
  dbms_scheduler.set_attribute(NAME => '"OUZY"."JOB1"'
                              ,attribute => 'stop_on_window_close'
                              ,VALUE => FALSE);
  dbms_scheduler.enable('"OUZY"."JOB1"');
END;

–强制打开窗口

BEGIN
  dbms_scheduler.open_window(window_name => 'WINDOW_TEST'
                            ,duration => '0 1:00:00'
                            ,force => TRUE);
END;

资源计划激活，作业运行。

–关闭窗口

BEGIN
  dbms_scheduler.close_window(window_name => 'WINDOW_TEST');
END;

作业类:
作业可以被指派一个类,类可以链接一个资源计划。
默认作业类: DEFAULT_JOB_CLASS

select * from dba_scheduler_jobs;
select * from dba_scheduler_schedules;
select * from dba_scheduler_programs;
select * from dba_scheduler_running_jobs;
select * from dba_scheduler_job_log;
select * from dba_scheduler_job_run_details;