complicated time schedule in dbms_job

[Lily_Liu_2004]

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

[jmodic]

The following will return you the next time your job should run:

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.

[paragp1981]

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

[TimHall]

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

[marist89]

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

[TimHall]

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

[Mr.Hanky]

Get a room guys!

[Lily_Liu_2004]

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.

[TimHall]

Get a room guys!

In the US or the UK?