complicated time schedule in dbms_job
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: complicated time schedule in dbms_job

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    complicated time schedule in dbms_job

    DBAs,

    I have to use dbms_job to schedule our statspack instead of the current cron job way. But I found that it is much harder to schedule a kinda complicated schedule in dbms_job than through cron job.

    For example;
    we want to submit a job to take a snapshot every 15 mineuets from 8am-18pm from Monday to Friday.
    How can I get this done in dbms_job?

    Thanks for the help.

    Lily

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The following will return you the next time your job should run:
    Code:
    SELECT CASE
             WHEN TO_CHAR(SYSDATE, 'Dy') IN ('Sat', 'Sun') OR
                  (TO_CHAR(SYSDATE, 'Dy') = 'Fri' AND TO_CHAR(SYSDATE, 'HH24') > '18')
                THEN TRUNC(NEXT_DAY(SYSDATE, 'Mon')) + 8/24
             WHEN TO_CHAR(SYSDATE, 'HH24') BETWEEN '00' AND '07' 
                THEN TRUNC(SYSDATE) + 8/24
             WHEN TO_CHAR(SYSDATE, 'HH24') > '18' THEN TRUNC(SYSDATE) + 1 + 8/24
             WHEN TO_CHAR(SYSDATE, 'MI') < 15 THEN TRUNC(SYSDATE, 'HH24') + 15/(24*60)
             WHEN TO_CHAR(SYSDATE, 'MI') < 30 THEN TRUNC(SYSDATE, 'HH24') + 30/(24*60)
             WHEN TO_CHAR(SYSDATE, 'MI') < 45 THEN TRUNC(SYSDATE, 'HH24') + 45/(24*60)
             ELSE TRUNC(SYSDATE, 'HH24') + 1/24
           END
    FROM dual;
    Now, you can use that messy CASE expression directly for your INTERVAL parameter of DBMS_JOB package, but a better approach would be to create a function or a view from this and call that function/view for your dbms_job interval.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    for 8am to 18pm you can take this precaution in procedure itself like foll
    ==============
    create or replace procedure xyz as
    begin

    if to_number(to_char(sysdate,'hh24')) not between 8 and 18 then
    raise_application_error(-20001,'This is not a valid time');
    end if;

    create the proc and just submit this proc as a job.
    put interval as

    ('sysdate+1/48') this will run script every half an hour. I dont know why but for 15 minutes its not working .

    See if this helps you

    Rgds
    Parag

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    What you really need to do is upgrade to 10g and use the new scheduler. It makes the definition of complex schedules are alot easier! ;-)

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Tim's too modest, but if you're getting into dbms_job pretty heavy, take a look at http://www.amazon.com/exec/obidos/tg...books&n=507846
    Last edited by marist89; 06-22-2005 at 12:05 PM.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    I wanted to plug the book, but I thought it was a little naughty using a public forum as an advertising tool.

    Luckily I have Jeff to do that for me

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Get a room guys!
    I remember when this place was cool.

  8. #8
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Thank you guys, especially thanks to jmodic.

    I used the way you recommended that put the select query into a view.
    It works fine.

    Thank a lot.

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Quote Originally Posted by Mr.Hanky
    Get a room guys!
    In the US or the UK?

    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width