This discussion is archived
5 Replies Latest reply: Nov 16, 2012 5:53 AM by 973458 RSS

Stop queue from scheduled job

973458 Newbie
Currently Being Moderated
Hi everybody,
I'm working on a Oracle 11g and I have a Scheduled Job running every 5 minutes.
When it lasts more then 5 minutes, it starts the next execution right after the previous.
Is there a way to prevent this behaviour? For example, if the 9.25 run ends at 9.32, the next run should be at 9.35.

Thanks,
best regards
  • 1. Re: Stop queue from scheduled job
    damorgan Oracle ACE Director
    Currently Being Moderated
    Very simple. Use DBMS_APPLICATION_INFO.SET_CLIENT_INFO and READ_CLIENT_INFO to set a flag that is checked by the job when it first starts. If the job detects a previous iteration is still running it can abort or reset the schedule and then abort.

    Demo here:
    http://www.morganslibrary.org/reference/pkgs/dbms_applic_info.html

    You could, of course, query DBA_SCHEDULER_RUNNING_JOBS but that is more overhead than what I suggest above.
  • 2. Re: Stop queue from scheduled job
    Dom Brooks Guru
    Currently Being Moderated
    You used to get such a slippage when using DBMS_JOB and the NEXT_DATE/INTERVAL parameters.
    This would have suited you because I think it was evaluated when the job completed - i.e. if the interval was five minutes and your 9:25 job finished at 9:32, then the next one would be 9:37.
    But I think they've even addressed that in DBMS_JOB.
  • 3. Re: Stop queue from scheduled job
    damorgan Oracle ACE Director
    Currently Being Moderated
    And that slippage is precisely why most of us moved from DBMS_JOB to DBMS_SCHEDULER as fast as we were able. It was far too common to find the 4:00am job running at 9:15am after a number of months.
  • 4. Re: Stop queue from scheduled job
    Dom Brooks Guru
    Currently Being Moderated
    It wasn't that big an issue ... certainly not if you used an expression that didn't slip.

    Don't get me wrong - DBMS_SCHEDULER is massively more powerful and flexible.
    But neither is DBMS_JOB completely redundant still (but I won't go off on a complete tangent).


    But it's rare to find someone who actually wants that slippage behaviour.
  • 5. Re: Stop queue from scheduled job
    973458 Newbie
    Currently Being Moderated
    Thanks to all of you for your replies.
    I think there is a misunderstanding, my needs are to avoid the slippage, so if a run lasts more then 5 minutes, it has to end naturally without being aborted, and the next will be skipped.
    If your solutions already answer my question, please forgive me and my bad reading.
    I add the code of my scheduled job:

    BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB
    (
    job_name => 'OWNER.JOBNAME'
    ,start_date => TO_TIMESTAMP_TZ('2012/04/10 07:00:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
    ,repeat_interval => 'FREQ=MINUTELY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22; BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55 ;'
    ,end_date => NULL
    ,job_class => 'DEFAULT_JOB_CLASS'
    ,job_type => 'PLSQL_BLOCK'
    ,job_action => ' --CODE OF PLSQL BLOCK'
    ,comments => NULL
    );
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'RESTARTABLE'
    ,value => FALSE);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'LOGGING_LEVEL'
    ,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'MAX_FAILURES');
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'MAX_RUNS');
    BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'STOP_ON_WINDOW_CLOSE'
    ,value => FALSE);
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'JOB_PRIORITY'
    ,value => 3);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'SCHEDULE_LIMIT');
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'OWNER.JOBNAME'
    ,attribute => 'AUTO_DROP'
    ,value => FALSE);

    SYS.DBMS_SCHEDULER.ENABLE
    (name => 'OWNER.JOBNAME');
    END;


    Thanks again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points