This discussion is archived
4 Replies Latest reply: Aug 20, 2012 10:44 AM by rp0428 RSS

Normal scheduler behavior?

Gaff Journeyer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    But of course! This is just some a play schedule/job while I look into the broader issues.
  • 3. Re: Normal scheduler behavior?
    rp0428 Guru
    Currently Being Moderated
    >
    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 Journeyer
    Currently Being Moderated
    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

Legend

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