Re: time shift in sheduled job

In article <G3B640.7Jw_at_news.boeing.com>,   "James" <jialong.x.xie_at_boeing.com> wrote:
> I need to run the scheduled job every two hours. So far so good. But I
> notice that there is a problem because the next_date has been shifting
 from
> the sharp o'clock after a few runs. For example, if I start the job at
 12:00
> noon, the next job will be running at about 2:01, the next one may be
 4:02.
> I can see that this will become serious after a few months. I can
 re-submit
> the job after a few days. But is there any better way to reset the
 next_date
> to sharp o'clock? Thanks in advance for any clue.
>
> Here is the script:
>
> BEGIN
> DBMS_JOB.ISUBMIT
> (1,'MY_PROC;',TRUNC(SYSDATE)+12/24,'SYSDATE+1/12',NULL);
> COMMIT;
> END;
>
>

I haven't tested this, so I don't know if it is entirely accurate. But, I do know that next_date and next_sec are calculated using the value specified in interval and the time the job ENDED, not when it began. So, my assumption is that the job runs in less than an hour. Try using the following specification for interval.

'to_char(sysdate, 'DD/MM/YYYY:HH24') + 1/12'

I believe this will add 2 hours to the 'hour' when the job ended, ignoring the value of 'minutes' altogether.

HTH,
Patrick

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 31 2000 - 15:32:25 CST