More Job Scheduling Examples with DBMS_JOB

OracleBurleson Consulting has some good examples of complex job scheduling.

Of particular interest is this example which causes a job to be run only Monday through Friday:

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE - 23/24,''MONDAY''),
next_day(SYSDATE - 23/24,''TUESDAY''),
next_day(SYSDATE - 23/24,''WEDNESDAY''),
next_day(SYSDATE - 23/24,''THURSDAY''),
next_day(SYSDATE - 23/24,''FRIDAY'')
)
,''HH'')',
TRUE,
:instno);
commit;
end;
/

If you’re not familiar with Burleson Consulting’s site dba-oracle.com you probably should be. They have some great articles on Oracle administration. They even have linked to Life After Coffee recently on this same topic.

If you’re looking for more general information on the DBMS_JOB package, check out my article Scheduling Jobs using Oracle’s Job Queue.

database, database administration, database development, oracle

13 thoughts on “More Job Scheduling Examples with DBMS_JOB”

  1. Always check stuff from Burleson Consulting for yourself before trusting it. Actually, that’s good advice REGARDLESS of the source, but there have been some unfortunate instances of incorrect advice being given out by BC (search AskTom or Jonathan Lewis’ site for specifics).

    Don, if you’re reading this, it’s just my opinion – don’t sue! 🙂

  2. Hi Jon,

    >> it’s time for a better picture of yourself for the site too.

    Easier said than done! I’m not exactly good-looking. I’m really fat from 25 years of sitting in front of a terminal all day, and I’m cursed with thick, stupid-looking bushy hair. I’ll go on a crash diet and get another pic. . . . Thanks!

    >> there have been some unfortunate instances of incorrect advice being given out . . .

    I work very hard to publish accurate information, and some of the beef with my PGA article was because I deliberately omitted “exceptions” relating to the MTS because few people use it anymore.

    I suppose some people would say that not noting all possible exceptions makes the content “incorrect”, but in Oracle-land there are so many exceptions that the main concept often gets lost within all of the caveats! I spend a lot of time teaching beginners, and I over-simplify so they can understand the concepts.

    Take care . . .

  3. Glad to see you get around in the blogosphere Don.

    >> I’m really fat from 25 years of sitting in front of a terminal

    Yeah, desk jobs aren’t kind to any of us. You’ll notice there aren’t any pictures of me on my site.

    My advice to everyone is never run anything in your databases that doesn’t make sense to you. I may find a site that has exactly the code I’m looking for, but I’ll pull out the books and read up on it until I’m comfortable with all the commands it uses, then I’ll run it. I’d rather not have to say to my boss that “I ran some code off the internet and I don’t understand what happened”.

  4. Never run anything in your databases that doesn’t make sense to you. I may find a site that has exactly the code I’m looking for, but I’ll pull out the books and read up on it until I’m comfortable with all the commands it uses, then I’ll run it.

    Well…I just had a conversation with Jon in my office and it seems he likes to use the ‘Force’ command all over the place saying “Well, [I don’t know what it means for sure], it seems to work nicely in some commands.”

  5. The important detail to the circumstances is that I was mentioning it to another DBA who should then go look up how it works before he uses it. I have looked it up before for specific commands and would look it up again before I used it.

  6. Here’s a funny thing – I stumbled on this posting when searching for an item where Don Burleson said that a comment I had made as “more than stupid” – and find an interesting take on the PGA article he wrote (for a different view point see: http://www.jlcomp.demon.co.uk/untested.html ) and explaining how

    But that’s irrelevant at the moment. A point I’d like to make is that before posting a statement that someone else’s code examples are GOOD, it benefits everyone if you check that the code does what it claims.

    The INTERVAL in the example you’ve quoted is defined as:

    ‘ trunc(
    least(
    next_day(SYSDATE,”MONDAY”),
    next_day(SYSDATE,”TUESDAY”),
    next_day(SYSDATE,”WEDNESDAY”),
    next_day(SYSDATE,”THURSDAY”),
    next_day(SYSDATE,”FRIDAY”)
    )
    +1/24,’HH”)’

    next_day(sysdate,’MONDAY’) returns a date value with the first Monday AFTER sysdate as the date component, and the current time as the time component.

    So taking the LEAST() of the list supplied will
    give you a date/time in the NEXT WORKING DAY (i.e. skipping week-ends)

    Adding 1/24 and truncating to the hour moves the time on to the next (complete) hour.

    So this INTERVAL gives you one report per day, skipping week-ends, and moving the time of the interval forward by one extra hour each day. It does not “run every hour, Monday – Friday”.

    Clearly, the code posted has not been tested properly.

    Regards

    Jonathan Lewis

  7. You’re right Jonathan, I did not test this code.

    Since the next_day wants to pick the next day AFTER the date passed we can just subtract 1 from sysdate and the interval will work.

    I have updated the code in this post and I will ping the folks over at Burleson to do the same.

    I feel it is better to put something out there with the risk of it being wrong than to not put anything out there at all. Even a wrong answer can help get someone to the right solution. In this case, someone using this information as a starting point as I have suggested in the past could take this code and get what they needed easily.

    Code is provided here (and at most sites) for educational purposes only. While that may seem like a boilerplate label to avoid litigation, it is the truth.

    That having been said I encourage you to continue to leave comments when you find errors. That is the great thing about blogging! The ability for the community to give feedback and help improve the content.

  8. Another Update: To keep this from running at midnight on Saturday morming and make it run at midnight on Monday morning I have changed it to subtract 23 hours and skip adding the extra hour.

    Don Burleson and folks point out that if the first running of this falls on a Saturday or Sunday it will not properly calculate the second running, but hey, you shouldn’t work on weekends anyway.

    Jon

  9. Hi.

    This seems to work fine for me:

    ‘trunc(least(next_day(SYSDATE – 23/24,”MONDAY”),next_day(SYSDATE – 23/24,”TUESDAY”),next_day(SYSDATE – 23/24,”WEDNESDAY”),next_day(SYSDATE – 23/24,”THURSDAY”),next_day(SYSDATE – 23/24,”FRIDAY”)),”HH”)’

    Of course, it’s much easier to use the 10g scheduler:

    ‘freq=hourly; byday=MON,TUE,WED,THU,FRI; byminute=0; bysecond=0;’

    🙂

    Cheers

    Tim…

  10. Thanks Tim. I’m looking forward to the 10g scheduler. We’re due to move most of our DBs to 10gR2 this summer.

    Jon

  11. Jon,

    Regarding: “Code is provided for educational purposes only”.
    I have no problem with people trying to explain something, and making the occasional mistake in the code they publish; after all an educational article that attempts to explain how something works may allow the reader to identify the error.

    I do have a bit of a problem with someone who directs people to a set of “good examples” without first checking the quality of those examples.

    I note that the original error that I highlighted has been corrected in the source article, but re-reada that source and take note:

    The very first example of dbms_job shows:
    what=>’statspack_alert.sql;’,
    That almost looks as if the author is suggesting that dbms_job can run SQL scripts direct, doesn’t it.

    The second example shows:
    DBMS_JOB.isubmit (
    Interesting – why have we switched to isubmit() – with no explanation.
    Moreoever, this example is decsribed as running to run once every hour, but it is the only one which does something like this:
    interval => ‘SYSDATE + 1/24
    Every other “once every hour” example uses
    trunc(SYSDATE+1/24,”HH”)’,
    Why the difference – and does it matter ?

    The third and fourth examples are annotated (respectively) with:
    — Submit job to begin at 0600
    — Submit job to begin at 0900
    but the code lines have there ‘next_date’ set to:
    trunc(sysdate)+6/24,
    trunc(sysdate+1)+9/24,
    So which one matches its description, and why ?

    The fourth example is annotated with
    — Submit job to begin at 0600 and run every 10 minutes
    but the code says:
    trunc(sysdate+1/144,’MI’),
    Silly error – but just one little warning among many.

    Then, the interval on this example is:
    ‘trunc(sysdate+1/144,”MI”)’,

    Now – is the interval calculated when the job starts, or when the job ends: and since the ‘waht’ was apparently a default call to:
    ’statspack.snap;’,
    what happens if the statspack.snap takes more than a minute – which can easily happen on a big system if you leave the statspack configuration to its default operation.

    By all means, do your best to educate your readers – but don’t tell them that something is good before you’ve checked it very carefully. They’re reading your material to find out how to do something; you owe it to them to make sure that the articles you direct them to are at least as good as the stuff you write yourself.

  12. I guess we have different philosophies Jonathan. I think these are good examples as they might give a person some ideas for writing their own jobs. They would serve the same purpose if they were written in pseudo-code.

    I do say that these are examples of complex job scheduling. I’d hope that someone who wants to use these examples has some understanding of the scheduler already, and therefore should be able to read past some of the inconsistencies.

Leave a Reply

Your email address will not be published. Required fields are marked *