4 Replies Latest reply: Aug 20, 2012 1:00 PM by Gaff RSS

    Normal scheduler behavior?

    Gaff
      I created a schedule to run a job every two minutes.

      BEGIN
        SYS.DBMS_SCHEDULER.DROP_JOB
          (job_name  => 'GAFF.DATEEVERY2MINS');
      END;
      /
      
      BEGIN
        SYS.DBMS_SCHEDULER.CREATE_JOB
          (
             job_name        => 'GAFF.DATEEVERY2MINS'
            ,start_date      => NULL
            ,repeat_interval => 'FREQ=MINUTELY'
            ,end_date        => NULL
            ,job_class       => 'DEFAULT_JOB_CLASS'
            ,job_type        => 'PLSQL_BLOCK'
            ,job_action      => 'BEGIN insert into GAFF.scheduler_output values(systimestamp,''SOME TEXT '' || systimestamp); END;'
            ,comments        => 'write date to TABLE every 2 mins'
          );
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'RESTARTABLE'
           ,value     => TRUE);
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'LOGGING_LEVEL'
           ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'MAX_FAILURES');
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'MAX_RUNS');
        BEGIN
          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
            ( name      => 'GAFF.DATEEVERY2MINS'
             ,attribute => 'STOP_ON_WINDOW_CLOSE'
             ,value     => FALSE);
        EXCEPTION
          -- could fail if program is of type EXECUTABLE...
          WHEN OTHERS THEN
            NULL;
        END;
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'JOB_PRIORITY'
           ,value     => 3);
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'SCHEDULE_LIMIT');
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'AUTO_DROP'
           ,value     => FALSE);
        SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
          ( name      => 'GAFF.DATEEVERY2MINS'
           ,attribute => 'RAISE_EVENTS'
           ,value     => SYS.DBMS_SCHEDULER.JOB_STARTED + SYS.DBMS_SCHEDULER.JOB_SUCCEEDED + SYS.DBMS_SCHEDULER.JOB_FAILED + SYS.DBMS_SCHEDULER.JOB_COMPLETED);
      END;
      /
      When I look at the source for the created objects in Toad I see that it created the following limit on the schedule itself.
      BEGIN
        SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
          (
            schedule_name    => 'GAFF.EVERY2MINS'
           ,start_date       => TO_TIMESTAMP_TZ('2012/09/11 12:36:15.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
           ,repeat_interval  => 'FREQ=MINUTELY;INTERVAL=2;'
           ,end_date         => TO_TIMESTAMP_TZ('2012/09/19 00:00:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
           ,comments         => 'Run these jobs every two minutes'
          );
      END;
      /
      I take that to mean that the schedule is not valid after 9/19. The job that I started three days ago was still running when I came in this morning though. So what I'd like to know is:
      1) What does the 9/19 date mean? No NEW jobs will be able to use the schedule after that date?
      2) How did that limit get in there in the first place from the attributes I set in my code?

      Version Info:

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE     10.2.0.4.0     Production
      TNS for Linux: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production

      ---

      Nevermind 2). It got there from the schedule create code (which I forgot to post but post here);
      BEGIN
        SYS.DBMS_SCHEDULER.DROP_SCHEDULE
          (schedule_name  => 'GAFF.EVERY2MINS');
      END;
      /
      
      BEGIN
        SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
          (
            schedule_name    => 'GAFF.EVERY2MINS'
           ,start_date       => TO_TIMESTAMP_TZ('2012/09/11 12:36:15.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
           ,repeat_interval  => 'FREQ=MINUTELY;INTERVAL=2;'
           ,end_date         => TO_TIMESTAMP_TZ('2012/09/21 00:00:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
           ,comments         => 'Run these jobs every two minutes'
          );
      END;
      /
      Edited by: Gaff on Aug 20, 2012 12:40 PM
        • 1. Re: Normal scheduler behavior?
          sb92075
          EXCEPTION
          -- could fail if program is of type EXECUTABLE...
          WHEN OTHERS THEN
          NULL;
          above is nice BUG waiting to bite folks!

          delete, remove, & eliminate all EXCEPTION code
          For reason why check these links.

          http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

          http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

          http://tkyte.blogspot.com/2007/03/challenge.html
          • 2. Re: Normal scheduler behavior?
            Gaff
            But of course! This is just some a play schedule/job while I look into the broader issues.
            • 3. Re: Normal scheduler behavior?
              rp0428
              >
              This is just some a play schedule/job while I look into the broader issues.
              >
              Why are you using an interval of 2 minutes for the SCHEDULE but 1 minute for the JOB?
              • 4. Re: Normal scheduler behavior?
                Gaff
                I was under the (mistaken?) impression that "MINUTELY", "DAILY" etc. were an indication of granularity. I mean, there isn't something called "BIMINUTELY" is there? So if I want 2 minutes, what else would I set that attribute to? (I'm asking, I have not played with SCHEDULER before).

                In any case, it has run every two minutes :)


                Thanks
                ---

                Looking at the SCHEDULE definition again, I think given that the granularity and repeat interval is set in that, then the JOB definition should probably just be null. I'm guessing it is ignored in this case because the job is assigned to a schedule that has an interval defined.

                Edited by: Gaff on Aug 20, 2012 1:59 PM