Oracle
強制鏈在完成後立即啟動,而不是在計時器上啟動
是否可以強制 Oracle 鏈在完成執行後立即重新啟動,而不是在計時器上?我只找到了進行臨時重啟的方法,這對我來說並不適用,因為這是一個具有不同完成時間的數據倉庫,不能設置為每 X 分鐘執行一次。
最簡單的方法是在鏈結束之前添加另一個步驟,更改作業以在 開始執行鏈
SYSDATE + 1/1440
。像這樣的東西:
CREATE OR REPLACE PROCEDURE move_next_execution AS BEGIN -- change to the correct job_id dbms_job.next_date(666,'sysdate+1/1440'); -- need to commit so that the scheduler (which runs in a different session) can see it commit; END; / sys.dbms_scheduler.create_program( program_name => 'PHIL.MOVE_EXEC', program_action => 'PHIL.move_next_execution', program_type => 'STORED_PROCEDURE', number_of_arguments => 0, comments => NULL, enabled => FALSE); sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.MOVE_EXEC');
(未經測試,但我有時間會測試它 - 不確定鍊和工作的結束是否會再次修改
next_date
.)我已經測試了以下,它工作正常(但涉及創建一個新工作):
-- schedule next execution CREATE OR REPLACE PROCEDURE schedule_next_execution AS BEGIN -- not that job_name will have to be generated so it's unique each time DBMS_SCHEDULER.CREATE_JOB ( job_name => 'chain_job', job_type => 'CHAIN', job_action => 'CHAIN', start_date => systimestamp + interval '1' minute, enabled => TRUE); END; / -- just an example proc CREATE OR REPLACE PROCEDURE do_nothing_for_2minutes AS BEGIN DBMS_LOCK.SLEEP(120); END; / BEGIN sys.dbms_scheduler.create_program( program_name => 'PHIL.NOTHING', program_action => 'PHIL.do_nothing_for_2minutes', program_type => 'STORED_PROCEDURE', number_of_arguments => 0, comments => NULL, enabled => FALSE); sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.NOTHING'); sys.dbms_scheduler.create_program( program_name => 'PHIL.SCHEDULE', program_action => 'PHIL.schedule_next_execution', program_type => 'STORED_PROCEDURE', number_of_arguments => 0, comments => NULL, enabled => FALSE); sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.SCHEDULE'); END; / BEGIN sys.dbms_scheduler.create_chain( comments => 'example chain', chain_name => 'PHIL.CHAIN' ); sys.dbms_scheduler.enable(name=>'PHIL.CHAIN'); END; / BEGIN sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( CHAIN_NAME => 'PHIL.CHAIN', STEP_NAME => 'STEP1', PROGRAM_NAME => 'PHIL.NOTHING' ); END; / BEGIN sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( CHAIN_NAME => 'PHIL.CHAIN', STEP_NAME => 'STEP2', PROGRAM_NAME => 'PHIL.SCHEDULE' ); END; / BEGIN sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( CHAIN_NAME => 'PHIL.CHAIN', condition => 'TRUE', action => 'START STEP1' ); END; / BEGIN sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( CHAIN_NAME => 'PHIL.CHAIN', condition => 'STEP1 SUCCEEDED', action => 'START STEP2' ); END; / BEGIN sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( CHAIN_NAME => 'PHIL.CHAIN', condition => 'STEP2 SUCCEEDED', action => 'END' ); END; / -- get timestamp select systimestamp from dual; -- run for first time BEGIN DBMS_SCHEDULER.RUN_CHAIN ( chain_name => 'CHAIN', start_steps => null ); END; / -- wait 2 mins select DBMS_LOCK.SLEEP(120) from dual; -- check for new job select start_date from dba_jobs where job_name = 'CHAIN_JOB';