Oracle

強制鏈在完成後立即啟動,而不是在計時器上啟動

  • December 13, 2012

是否可以強制 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';

引用自:https://dba.stackexchange.com/questions/30373