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 <sfaroult_at_oriole.com> 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: sfaroult_at_oriole.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).
>
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: barbarabbaker_at_yahoo.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: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 10 2003 - 21:48:40 CST
