Re: dbms_job - running jobs every 15 minutes

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Tue, 21 Jan 2003 18:23:44 -0800
Message-ID: <F001.00536DCF.20030121182344@fatcity.com>

Jared

in general it's much more better to use bind variables, here I do not use them, shame on me! :)

Hope the code below is ok for you at least it does not look so complicated.

VARIABLE jobno NUMBER;
VARIABLE plsql VARCHAR2(1000);
EXEC :plsql := 'BEGIN statspack.snap; END;'; BEGIN
   dbms_job.submit(
     :jobno

   , :plsql
   , TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'SSSSS') / 900) / 96)
   , 'TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, ''SSSSS'') / 900) / 96)'

   );
   COMMIT;
END;
/

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.


Jared.Still_at_radisys.com wrote:


> Feeling particularly anal the other day,  I used the following 

> specification to

> run statspack at the top of the hour, 15, 30 and 45 minutes after the 

> hour.

> 

> variable jobno number;

> variable instno number;

> begin

>         select instance_number into :instno from v$instance;

>         dbms_job.submit(

>                 :jobno

>                 , 'statspack.snap;'

>                 -- every 15 minutes at 00,15,30 and 45

>                 , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * 

> floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))

>                 , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * 

> floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'

>         );

>         commit;

> end;

> /

> 

> 

> Seems to me that the time specs could be simplified a bit.

> 

> Anyone care to give it a go?  :)

> 

> Jared

> 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jan 21 2003 - 20:23:44 CST