RE: RE: dbms_job - running jobs every 15 minutes
How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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 Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - +trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 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 *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: dbms_job - running jobs every 15 minutes
That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Arup Nanda INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 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
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Robert, I have solid reasons not to trust dbms_job ... it didn't work reliably in 901x. Call me retro ... but "cron" rocks ... 8:) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re:RE: dbms_job - running jobs every 15 minutes
One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Dick Goulet Reply Separator Author: Freeman Robert - IL [EMAIL PROTECTED] Date: 1/22/2003 7:09 AM Cron? How 1980's :-)) RF 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 7:19 AM To: Multiple recipients of list ORACLE-L I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L 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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 TITLERE: dbms_job - running jobs every 15 minutes/TITLE META content=MSHTML 6.00.2800.1126 name=GENERATOR/HEAD BODY DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2Cron? How 1980's :-))/FONT/SPAN/DIV DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2/FONT/SPANnbsp;/DIV DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2RF/FONT/SPAN/DIV DIVnbsp;/DIV PFONT face=Verdana size=2Robert G. Freeman/FONTFONT face=Times New RomanBR/FONTFONT face=Verdana size=2Technical Management ConsultantBRTUSC - The Oracle Experts www.tusc.comBR904.708.5076 Cell (it's everywhere that I am!)BRAuthor of several books you can find on Amazon.com!/FONT /P BLOCKQUOTE dir=ltr style=MARGIN-RIGHT: 0px DIV class=OutlookMessageHeader dir=ltr align=leftFONT face=Tahoma size=2-Original Message-BRBFrom:/B Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]BRBSent:/B Wednesday, January 22, 2003 7:19 AMBRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B RE: dbms_job - running jobs every 15 minutesBRBR/FONT/DIV PFONT size=2I simplified it by using cron instead ... lt;ggt;/FONT /P PFONT size=2Raj/FONT BRFONT size=2__/FONT BRFONT size=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT size=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT size=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONTBRFONT size=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /PBR PFONT size=2-Original Message-/FONT BRFONT size=2From: [EMAIL PROTECTED] [A href=mailto:[EMAIL PROTECTED];mailto:[EMAIL PROTECTED]/A]/FON T BRFONT size=2Sent: Tuesday, January 21, 2003 7:24 PM/FONT BRFONT size=2To: Multiple recipients of list ORACLE-L/FONT BRFONT size=2Subject: dbms_job - running jobs every 15 minutes/FONT /PBR PFONT size=2Feeling particularly anal the other day,nbsp; I used the following /FONTBRFONT size=2specification to/FONT BRFONT size=2run statspack at the top of the hour, 15, 30 and 45 minutes after the /FONTBRFONT size=2hour./FONT /P PFONT size=2variable jobno number;/FONT BRFONT size=2variable instno number;/FONT BRFONT size=2begin/FONT BRFONT size=2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; select instance_number into :instno from v$instance;/FONT BRFONT size=2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; dbms_job.submit(/FONT BRFONT size=2nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp ;nbsp;nbsp;nbsp; :jobno/FONT BRFONT size=2nbsp;nbsp
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes Cron? How RELIABLE !! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 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
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes LOL. Ok, I confess, my name's Robert and I'm a CRON user Rf Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How RELIABLE !! -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes Cron? How 1980's :-)) RF Robert G. FreemanTechnical Management ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of several books you can find on Amazon.com! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_job - running jobs every 15 minutes I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 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
RE: dbms_job - running jobs every 15 minutes
One thing I've learned Arup, there are 15,000 ways of doing the same thing, and a good many of those are as good as the other. Cron, dbms_job, at, whatever works for you!! Rf 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 8:55 AM To: Multiple recipients of list ORACLE-L That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Arup Nanda INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: dbms_job - running jobs every 15 minutes
Title: RE: dbms_job - running jobs every 15 minutes
LOL. You are right about the job_scheduler in early
9i.
Had
all sorts of problems when I first started moving stuff to
9.0.1
and 9.0.2 and Oracle was no help figuring out what the
problem was.
I use
cron all the time, but I love the job scheduler for things
like
starting parallel PL/SQL threads, etc It's just so easy!
;-)
Hey, I
*LIKE* retro, listen to the 80's station all the time!
RF
Robert G. FreemanTechnical Management
ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's
everywhere that I am!)Author of several books you can find on
Amazon.com!
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22,
2003 9:49 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: dbms_job - running jobs every 15
minutes
Robert,
I have solid reasons not to trust dbms_job ... it didn't work reliably
in 901x. Call me retro ... but "cron" rocks ...
8:)
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
dot com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts,
but having an opinion is an
art!
-Original Message-From: Freeman Robert - IL
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
dbms_job - running jobs every 15 minutes
Cron? How 1980's :-))
RF
Re: dbms_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job -- INTERVAL is a date function, evaluated immediately before the job starts -- executing... runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re[2]: dbms_job - running jobs every 15 minutes
Vladimir, I beg to disagree. The reason is that we used a number of jobs where the interval was defined as sysdate+1 and the job routinely ran for ~30 minutes every day. The result was that the job migrated over the course of a week by 3.5 hours so that instead of running at 6AM as scheduled on Monday it was running at 9:30 AM on the next Monday. I filed an iTar on the subject which resulted in OTS providing what I stated. Interval is evaluated at the end of the job, not the start. Dick Goulet Reply Separator Author: Vladimir Begun [EMAIL PROTECTED] Date: 1/22/2003 9:40 AM [EMAIL PROTECTED] wrote: One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job -- INTERVAL is a date function, evaluated immediately before the job starts -- executing... runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: RE: dbms_job - running jobs every 15 minutes
Yea... I realized that after I sent the email, so I submitted my second suggestion... Thanks though for your thoughts!! 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 2:45 AM To: Multiple recipients of list ORACLE-L How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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 Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
Well, I did consider that using 4 jobs , but my overdeveloped sense of aesthetics and professional hubris (that one mostly) required that it be done with a single job. ;) Lots of good suggestions here. Seems like everyone is always up for a puzzle. :) Jared Arup Nanda [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 06:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: dbms_job - running jobs every 15 minutes That's exactly what I do. Phew! I thought I was the only one ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 8:03 AM Personally, I tend to just submit four jobs: one at the top of hour, one at 15 past, one at 30 past, and the fourth at 45 past. To alter the frequency, just break or remove one or more of the jobs. Falls into the category of not elegant, but it works... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 9:39 PM 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Arup Nanda INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
RE: dbms_job - running jobs every 15 minutes
Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: RE: dbms_job - running jobs every 15 minutes
Hmm... I had only given the code a cursory examination. Good thing I didn't use it. :) Jared Freeman Robert - IL [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 10:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: dbms_job - running jobs every 15 minutes Yea... I realized that after I sent the email, so I submitted my second suggestion... Thanks though for your thoughts!! 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 2:45 AM To: Multiple recipients of list ORACLE-L How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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 Robert, I am afraid that you will soon run into the 'slipping job' syndrom. Without any 'trunc' (or anything functionally similar), 'sysdate' in the interval happens to be the date when the job started - which may be up to one minute (usually) the time when you asked it to start. Means that you can easily slip by four minutes every hour. I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be 'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering that a quarter of an hour is a 96th (24 * 4) of a day you have several more or less complicated ways to do it. Vladimir (whose formula I am still trying to understand :-)) took the seconds since midnight, you can also do something such as [today 00:00] trunc(sysdate) + [current time rounded to the latest quarter of an hour] floor((sysdate - trunc(sysdate))* 96) / 96 + 15/1440 HTH, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
Re:RE: dbms_job - running jobs every 15 minutes
Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ... Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 08:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:RE: dbms_job - running jobs every 15 minutes One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Dick Goulet Reply Separator Author: Freeman Robert - IL [EMAIL PROTECTED] Date: 1/22/2003 7:09 AM Cron? How 1980's :-)) RF 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 7:19 AM To: Multiple recipients of list ORACLE-L I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L 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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 TITLERE: dbms_job - running jobs every 15 minutes/TITLE META content=MSHTML 6.00.2800.1126 name=GENERATOR/HEAD BODY DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2Cron? How 1980's :-))/FONT/SPAN/DIV DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2/FONT/SPANnbsp;/DIV DIVSPAN class=56935-22012003FONT face=Arial color=#ff size=2RF/FONT/SPAN/DIV DIVnbsp;/DIV PFONT face=Verdana size=2Robert G. Freeman/FONTFONT face=Times New RomanBR/FONTFONT face=Verdana size=2Technical Management ConsultantBRTUSC - The Oracle Experts www.tusc.comBR904.708.5076 Cell (it's everywhere that I am!)BRAuthor of several books you can find on Amazon.com!/FONT /P BLOCKQUOTE dir=ltr style=MARGIN-RIGHT: 0px DIV class=OutlookMessageHeader dir=ltr align=leftFONT face=Tahoma size=2-Original Message-BRBFrom:/B Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]BRBSent:/B Wednesday, January 22, 2003 7:19 AMBRBTo:/B Multiple recipients of list ORACLE-LBRBSubject:/B RE: dbms_job - running jobs every 15 minutesBRBR/FONT/DIV PFONT size=2I simplified it by using cron instead ... lt;ggt;/FONT /P PFONT size=2Raj/FONT BRFONT size=2__/FONT BRFONT size=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT size=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT size=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONTBRFONT size=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /PBR PFONT size=2-Original Message-/FONT BRFONT size=2From: [EMAIL PROTECTED] [A href=mailto:[EMAIL PROTECTED];mailto:[EMAIL PROTECTED]/A]/FON T BRFONT size=2Sent: Tuesday, January 21, 2003 7:24 PM/FONT BRFONT size=2To: Multiple recipients of list ORACLE-L/FONT BRFONT size=2Subject: dbms_job - running jobs every 15 minutes/FONT /PBR PFONT size=2Feeling particularly anal the other day,nbsp; I used the following /FONTBRFONT
RE: dbms_job - running jobs every 15 minutes
Thanks! Works better than the first suggestion. RF 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 12:35 PM To: Multiple recipients of list ORACLE-L Thanks Robert, I like this. Simplified and still easy to read. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ... Jared, I wanted to ask this question before but just provided a solution w/o talking too much :) If it's regardless of runtime (it means potentially one job can consume more than 15 minutes to get things done) is it allowed to run jobs concurrently? In case of positive asnwer, you need 4 jobs. Otherwise in case of more than 15mins runtime you'll face slipped jobs. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: dbms_job - running jobs every 15 minutes
As an aside, if you want something to run twice a day at the same time (in my case 11:50 AM/PM), this seems to work well for an interval: trunc(sysdate) + (decode(to_char(sysdate,'AM'),'AM',1,2)*12+(11+(50/60)))/24 Enjoy! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 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: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:RE: dbms_job - running jobs every 15 minutes
Actually, Interval is evaluated at the beginning of the job according to the docs. I've not seen anyone mention the real cause behind DBMS_Job creep. That is the setting of Job_Queue_Interval which, by default, is 60 seconds. So your jobs will run 1 minute later each time unless you set Interval to evaluate to an absolute. If someone's set Job_Queue_Interval longer, the creep will be longer as well. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] Sent by: To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: Re:RE: dbms_job - running jobs every 15 minutes 01/22/2003 10:34 AM Please respond to ORACLE-L One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Dick Goulet -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: dbms_job - running jobs every 15 minutes
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
If a job takes longer than the next scheduled time to execute then I see a problem. Suppose a job runs every 15 mins but runtime is 30 mins, the number ofjobs will increase andcompete for the same resources. I always use cron (80's kinda of control), and the 1st thing I do is check if the job is running and if it is, I exit and issue a warning email. Gene [EMAIL PROTECTED] 01/22/03 02:34PM [EMAIL PROTECTED] wrote:Now as discussed, if the job is scheduled to start at 9:00 AM andrunbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ...Jared, I wanted to ask this question before but just provideda solution w/o talking too much :)If it's regardless of runtime (it means potentially one jobcan consume more than 15 minutes to get things done) is itallowed to run jobs concurrently?In case of positive asnwer, you need 4 jobs. Otherwise in caseof more than 15mins runtime you'll face slipped jobs.Regards,-- Vladimir BegunThe statements and opinions expressed here are my own anddo not necessarily represent those of Oracle Corporation.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Vladimir Begun INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/21/2003 08:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: dbms_job - running jobs every 15 minutes
1980's??? Yes, but it works, ain't broke, reliable, KISS, and is easy to implement. I use cron from my Linux box to connect to the production Oracle database (OpenVMS) and do a RMAN backup with the catalog on a second Oracle database (OpenVMS) server. Beats the heck out of trying to get the backup scritp to rewrite its' self after each use. If either server has problems there are entries in the log. Ron [EMAIL PROTECTED] 01/22/03 10:09AM Cron? How 1980's :-)) RF 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 7:19 AM To: Multiple recipients of list ORACLE-L I simplified it by using cron instead ... g Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L 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: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
Stephane Faroult wrote:
Vladimir (whose formula I am still trying to understand :-))...
TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'S') / 60 / :interval) / (24
* 60 / :interval));
P.S.: could you please answer my question ('100% CPU utilization,
urgent') thread?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
Vladamir, This job runs pretty fast, so I don't worry about jobs running into each other. It's a level 0 statspack.snapshot, it has run 407 seconds in ~ 700 executions. Jared Vladimir Begun [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: dbms_job - running jobs every 15 minutes [EMAIL PROTECTED] wrote: Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ... Jared, I wanted to ask this question before but just provided a solution w/o talking too much :) If it's regardless of runtime (it means potentially one job can consume more than 15 minutes to get things done) is it allowed to run jobs concurrently? In case of positive asnwer, you need 4 jobs. Otherwise in case of more than 15mins runtime you'll face slipped jobs. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:RE: dbms_job - running jobs every 15 minutes
Oracle does not guarantee that the job will start at the time specified; it seems that it usually starts about 1 minute later, at least that's what I see. This would account for the creep, as when the interval is evaluated, it is later than you might expect. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2003 11:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:RE: dbms_job - running jobs every 15 minutes Actually, Interval is evaluated at the beginning of the job according to the docs. I've not seen anyone mention the real cause behind DBMS_Job creep. That is the setting of Job_Queue_Interval which, by default, is 60 seconds. So your jobs will run 1 minute later each time unless you set Interval to evaluate to an absolute. If someone's set Job_Queue_Interval longer, the creep will be longer as well. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] Sent by: To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: Re:RE: dbms_job - running jobs every 15 minutes 01/22/2003 10:34 AM Please respond to ORACLE-L One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Dick Goulet -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
dbms_job - running jobs every 15 minutes
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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: dbms_job - running jobs every 15 minutes
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, 'S') / 900) / 96) , 'TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, ''S'') / 900) / 96)' ); COMMIT; END; / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: dbms_job - running jobs every 15 minutes
How about dbms_job.submit(:jobno, 'statspack.snap', sysdate+n/1440, 'sysdate 15/1440'); where n= a number of minutes to the nearest 15 minutes. So if it's 14:25 then it would be sysdate+5/1440. since you only need to do this one time, just make sure that sysdate + n = 0, 15, 30 or 45 after the hour... :-) Of course, if you want to automate the thing, then build this around a PL/SQL procedure...that calculates the value of n. Not elegant, but I think that when someone looks at DBA_JOBS they are not going to ask what the $*#(@( you were trying to do.. I subscribe to the KISS philosophy... :-) RF -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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/ hh24'), 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''), ''mm/dd/ hh24'') + 15/1440' ); -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 6:24 PM To: Multiple recipients of list ORACLE-L 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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Robert Freeman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
