-- 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) |