5 Replies Latest reply: Nov 16, 2012 7:53 AM by 973458 RSS

    Stop queue from scheduled job

    973458
      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
          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
            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
              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
                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
                  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.