I do this with a little trick:
interval => 'sysadte + 15/1440',
what => 'if to_char(sysdate,'hh24') < 8 or to_char(sysdate,'hh24') > 20
then
do_something;
end if'
Remember, you cannot be sure when the job will be executed next. E.g. if
you have an interval of 'trunc(sysdate+1) + 20/24' you may think the job
will always be executed at 8 PM. But if other jobs are executed then or db
is down or .... then whe job will be executed whenever possible. So, do
the important checks in job's body.
Yavor
On Wed, 26 Oct 2005 18:15:02 +0300, <t_adolph@xxxxxxxxxxx> wrote:
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
-- //www.freelists.org/webpage/oracle-l
