oracle强制停止scheduler,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;

阅读(1715) | 评论(0) | 转发(0) |