Does this work for you: select trunc(sysdate + 1/24,'hh') + 50/(60 * 24) from dual;
Your next will be: 'trunc(sysdate + 1/24,'hh') + 50/(60 * 24)' Regards, Waleed -----Original Message----- Sent: Friday, January 10, 2003 9:09 PM To: Multiple recipients of list ORACLE-L Stephane: Precisely what I was after! Thanks so much for your help. I really appreciate it. Barb --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > Barbara Baker wrote: > > > > Sun / Solaris 2.6 ; Oracle RDBMS v8.0.5.2.1 > > > > List: > > I want to change the perfstat statspack.snap > procedure > > FROM running every hour on-the-hour > > TO running at 10 minutes till the hour. > > (On-the-hour is conflicting with another > procedure.) > > I tried to Read The Frustrating Manual, but I can > only > > find examples for on-the-hour intervals. > > > > I thought I had it nailed when I spoon-fed the job > > with the next_date value at 10 till, and then the > > interval for 'SYSDATE+1/24'. When I looked at it > > after the next run, it was fine, but it has > "creeped". > > Now it's running whenever it feels like it. > > > > I've lost track of all the date intervals I tried, > but > > here's what I tried last. > > > > variable jobno number; > > begin > > select 1 into :jobno from dual; > > end; > > / > > begin > > dbms_job.change(:jobno, > > 'statspack.snap;', > > to_date('09-JAN-2003 9:50') , > > 'SYSDATE+1/24' > > ); > > commit; > > end; > > / > > > > prompt Job number for automated statistics > collection > > for this instance > > prompt > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > prompt Note that this job number is needed when > > modifying or removing > > prompt the job: > > print jobno > > > > Here's what it currently looks like: > > > > What: statspack.snap; > > Job: 1 > > Priv User: PERFSTAT > > Last: 01/10/2003 14:16:13 > > Running: NO > > Next: 01/10/2003 15:16:13 > > Interval: SYSDATE+1/24 > > Total Time: > 53985.9999999999999999999999999999999999 > > Broken: > > > > Any idea how I can get to run consistently at 10 > till > > each hour? > > > > Thanks in advance for any help. > > Barb > > > > Barbara, > > Jobs creep because Oracle polls for jobs every > minute by default - > more or less. Then it computes 'next run' time, runs > the job and, if it > works, updates next_date. The problem is that you > job starts say only 20 > seconds late (based on the 'theoretical' time) this > lag accumulates if > you compute the next time as SYSDATE + 1/24. Assume > 20 s each time on > average, it's 8 minutes every day - quickly becomes > quite noticeable. > The only way to keep things under check is to use > TRUNC() to the > suitable unit. You should define INTERVAL as > TRUNC(SYSDATE, 'HH24') + 1/24 + 10/1440 > ^ round time (current) > ^ plus one hour > ^ plus ten > minutes > > -- > Regards, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Stephane Faroult > 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). > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Khedr, Waleed 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).