RE: schedule a job every 15 mins *but* only during the office hours
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <t_adolph@xxxxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Oct 2005 10:22:03 -0400
Tony,
I've not tried to be so sophisticated, but the following has worked
interval=> 'decode(rtrim(to_char(sysdate,''DAY'')),''SUNDAY'',
sysdate+1,''SATURDAY'',sysdate+1, sysdate+.5)'
Watch the quotes else you get syntax errors. My best was to place the
decode in a variable and then set interval =>'&nd'
_____
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of t_adolph@xxxxxxxxxxx
Sent: Wednesday, October 26, 2005 11:15 AM
To: ORACLE-L
Subject: schedule a job every 15 mins *but* only during the office hours
Hi all,
I'm trying to setup snapshots to run every 15 minutes during working
hours,
else hourly. I'm ignoring weekends for now,...
Origonally I tried:
var job number;
begin
sys.dbms_job.submit(
job => :job,
what => 'statspack.snap();',
next_date => trunc(sysdate,'HH24') + 1/24,
interval => case
when (to_char(sysdate,'hh24') between 8 and 20) then
'trunc(sysdate,''MI'') + 15/1440' -- 15 mins
else
'trunc(sysdate,''HH24'') + 1/24'
end
);
commit;
end;
/
but it seems that interval will only be set once, i.e. when the dbms_job
is actually submitted.
And not each "run", the evaluation of the case statement is stored and
not the statement
its self.
I then tried using a function instead of sql, one which returned a date
string for the next time to
run. But it is only evaluated the once as above.
I tried a similar approach with next_date, but got syntax errors.
Can it be done this way? I've written a work-round using a wrapper for
snap():
I schedule procedure every 15 min and it works out whether it should
call a snap or not:
CREATE OR REPLACE PROCEDURE snapIf(
p_now date default sysdate,
p_startOfDay_HH24 number default 8,
p_endOfDay_HH24 number default 17, -- includes 17:59
p_startOfWeekD number default 2, -- Monday
p_endOfWeekD number default 6 -- Friday
) is
begin
if (to_char(p_now,'HH24') between p_startOfDay_HH24 and
p_endOfDay_HH24)
and (to_char(p_now,'D') between p_startOfWeekD and p_endOfWeekD)
then
--dbms_output.put_line('snap during week');
statspack.snap();
elsif to_char(p_now,'MI') = 0 -- on the hour outside office hours
then
--dbms_output.put_line('snap on the hour');
statspack.snap();
else
--dbms_output.put_line('no snap');
null;
end if;
end;
Any thoughts...
Cheers
Tony
Other related posts:
