RE: dbms_job - running jobs every 15 minutes

I like this solution...... Looking at the second one I proposed, I think that it actually would end up just running once an hour at 15 after.... It's amazing how things look a lot different at midnight and 2pm in the afternoon.

Cheers!

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com!

-----Original Message-----
Sent: Wednesday, January 22, 2003 1:39 PM To: Multiple recipients of list ORACLE-L

The problem with that formula is that you'll get "creep" and eventually your 15-minute jobs are running 8 minutes late.

Try this one instead:

trunc(sysdate) + (trunc(to_number(sysdate - trunc(sysdate)) * (60/15*24)) / (60/15*24)) + 1/(60/15*24)

Guaranteed to run every 15 on the 15. At least I think so... The algorithm comes courtesy the TOAD group from Quest, via the TOAD mailling list, and us here at QTI.

Enjoy! :)
Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
> Sent: Wednesday, January 22, 2003 12:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: dbms_job - running jobs every 15 minutes
>
>
> Thanks Robert, I like this.
>
> Simplified and still easy to read.
>
> Jared
>
>
>
>
>
>
> "Robert Freeman" <robertgfreeman_at_YAHOO.COM>
> Sent by: root_at_fatcity.com
> 01/21/2003 08:39 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: dbms_job - running jobs every 15 minutes
>
>
> How about this... this will start the job at the top of the
> following hour
> and then schedule it every 15 minutes...
>
> dbms_job.submit(:jobno,
> 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/yyyy hh24'),
> 'mm/dd/yyyy hh24')
> ,'to_date(to_char(sysdate+60/1440,''mm/dd/yyyy hh24''),
> ''mm/dd/yyyy hh24'') + 15/1440' );

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: FREEMANR_at_tusc.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).