主页 / dba / 问题 / 30373
Accepted
Woot4Moo
Asked: 2012-12-14 07:12:36 +0800 CST

强制链在完成后立即启动，而不是在计时器上

  • 772

是否可以强制 Oracle 链在完成执行后立即重新启动，而不是在计时器上？我只找到了临时重启的方法，这对我来说真的不起作用，因为这是一个完成时间不同的数据仓库，不能设置为每 X 分钟运行一次。

oracle data-warehouse

1 个回答

  1. Best Answer

    最简单的方法是在链结束之前添加另一个步骤，将作业更改为在 开始执行链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';
    • 2

相关问题