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

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

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

From: <t_adolph_at_hotmail.com>
Date: Wed, 26 Oct 2005 16:15:02 +0100
Message-ID: <BAY103-DAV747F919D7421E427842BDFA690@phx.gbl>


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:16:16 CDT

Original text of this message

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