Oracle Scheduler的深入使用

-- 1.SYS赋权
grant create job to rm;
grant create evaluation context to rm;          -- create chain要的权限
grant create rule set to rm;                    -- create chain要的权限
grant create rule to rm;                        -- define_rule_set要的权限，比较危险，不建议使用，使用system或sys用户来定义

在Unix平台上执行external的PROGRAM，则需要配置$ORACLE_HOME/bin目录及其父目录都要为a+rx
So for e.g. if your $ORACLE_HOME is /opt/oracle/db then you would have to make sure that
chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

-- 2.创建计划
begin
   dbms_scheduler.create_schedule(
     schedule_name => 'RM_DAILY_SCHEDULE',
     start_date => systimestamp at time zone 'PRC',
     repeat_interval=> 'FREQ=DAILY;BYHOUR=20;BYMINUTE=08;BYSECOND=0',
     comments=>'RM JOB SCHEDULE WITH CHAIN DAILY');
end;
/

-- 3.创建program, 注意不要使用exec，会出错
begin
  dbms_scheduler.create_program(
     program_name=> 'FLASH_REPORT_MVIEW',
     program_type=> 'PLSQL_BLOCK',
     program_action=> 'begin
                         dbms_mview.refresh(''V_GREPORT_GJZYYBB'', method => ''complete'');
                       end;',
     enabled => true);
end;
/

begin
  dbms_scheduler.create_program(
     program_name=> 'BACKUP_ORACLE_LOG',
     program_type=> 'EXECUTABLE',
     program_action=> '/oracle/orasrc/backup/bak_oralog.sh',
     enabled => true,
     number_of_arguments => 0);
end;
/



-- 4.创建chain
BEGIN
  dbms_scheduler.create_chain(
    chain_name =>'RM_CHAIN_DAILY',
    rule_set_name => NULL,
    evaluation_interval => NULL);

  dbms_scheduler.enable('RM_CHAIN_DAILY');
END;
/


-- 5.定义步骤
BEGIN
  dbms_scheduler.define_chain_step(
    chain_name =>'RM_CHAIN_DAILY',
    step_name =>'STEP1_FLASH_REPORT_MVIEW',
    program_name =>'FLASH_REPORT_MVIEW');

  dbms_scheduler.alter_chain(
    chain_name =>'RM_CHAIN_DAILY',
    step_name =>'STEP1_FLASH_REPORT_MVIEW',
    attribute=>'skip',
    value=>FALSE);
END;
/


BEGIN
  dbms_scheduler.define_chain_step(
    chain_name =>'RM_CHAIN_DAILY',
    step_name =>'STEP2_BACKUP_ORACLE_LOG',
    program_name =>'BACKUP_ORACLE_LOG');

  dbms_scheduler.alter_chain(
    chain_name =>'RM_CHAIN_DAILY',
    step_name =>'STEP2_BACKUP_ORACLE_LOG',
    attribute=>'skip',
    value=>FALSE);
END;
/

-- 6.定义chain规则
BEGIN
  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'RM_CHAIN_DAILY',
    condition  => 'TRUE',
    action     => 'START STEP1_FLASH_REPORT_MVIEW',
    rule_name  => 'RM_CHAIN_DAILY_RULE_START',
    comments   => 'First link in the chain.');

  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'RM_CHAIN_DAILY',
    condition  => 'STEP1_FLASH_REPORT_MVIEW completed',
    action     => 'END',
    rule_name  => 'RM_CHAIN_DAILY_RULE_END',
    comments   => 'End of the chain.');
END;
/


BEGIN
  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'RM_CHAIN_DAILY',
    condition  => 'TRUE',
    action     => 'START STEP1_FLASH_REPORT_MVIEW',
    rule_name  => 'RM_CHAIN_DAILY_RULE_START',
    comments   => 'First link in the chain.');

  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'RM_CHAIN_DAILY',
    condition  => 'STEP1_FLASH_REPORT_MVIEW completed',
    action     => 'START STEP2_BACKUP_ORACLE_LOG',
    rule_name  => 'RM_CHAIN_DAILY_RULE_1',
    comments   => 'Next of the chain');

  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'RM_CHAIN_DAILY',
    condition  => 'STEP2_BACKUP_ORACLE_LOG completed',
    action     => 'END',
    rule_name  => 'RM_CHAIN_DAILY_RULE_END',
    comments   => 'End of the chain.');
END;
/



-- 7.创建JOB
BEGIN
dbms_scheduler.create_job(
  job_name => 'RM_DAILY_JOB',
  job_type => 'CHAIN',
  job_action => 'RM_CHAIN_DAILY',
  schedule_name => 'RM_DAILY_SCHEDULE',
  job_class => 'DEFAULT_JOB_CLASS',
  auto_drop => FALSE,
  enabled => TRUE);
END;
/



select * from dba_scheduler_programs;
select * from dba_scheduler_jobs;
select * from dba_scheduler_running_jobs;
select * from dba_scheduler_running_chains;
select * from dba_scheduler_job_log;
select * from dba_mview_refresh_times;
select * from dba_scheduler_running_jobs;