-
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
-
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?
-
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
-
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'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
-
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...
-
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.
-
Originally Posted by Mr.Hanky
Get a room guys!
In the US or the UK?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|