complicated time schedule in dbms_job
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.
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.
The following will return you the next time your job should run:
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.
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
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
for 8am to 18pm you can take this precaution in procedure itself like foll
create or replace procedure xyz as
if to_number(to_char(sysdate,'hh24')) not between 8 and 18 then
raise_application_error(-20001,'This is not a valid time');
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
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! ;-)
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.
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
I remember when this place was cool.
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.
In the US or the UK?
Originally Posted by Mr.Hanky
Click Here to Expand Forum to Full Width