Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: schedule a job every 15 mins *but* only during the office hours

Re: schedule a job every 15 mins *but* only during the office hours

From: Yavor Ivanov <Yavor_Ivanov_at_stemo.bg>
Date: Wed, 26 Oct 2005 17:26:21 +0300
Message-ID: <op.sy9bp7h3tjan04@theshire.mshome.net>

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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 26 2005 - 09:28:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US