Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to force an Oracle chain to restart immediately after it finishes executing, instead of on a timer? I have only found ways to do a temporal restart, which doesn't really work for me as this is a data warehouse that has varying times of completion and cannot be set to just run every X minutes.

share|improve this question
add comment

1 Answer

The easiest way to do this is to add another step prior to the end of the chain that changes the job to start execution of the chain at SYSDATE + 1/1440.

Something like this:

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

(Untested, but will test it when I have time - unsure if the end of the chain & job will again modify the next_date.)

I have tested the following, which works OK (but involves creating a new job):

-- 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';
share|improve this answer
    
Could you elaborate on this? It seems when I add the END condition to the chain that would signal the end of execution. What I don't want is for the chain to never properly terminate. –  Woot4Moo Dec 13 '12 at 20:49
    
Edited answer... –  Phil Dec 13 '12 at 21:44
    
Let me test this tomorrow and see what kind of resources it eats, I misconfigured my chain earlier today and nearly killed OEM –  Woot4Moo Dec 13 '12 at 21:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.