Re: time shift in sheduled job

From: James <jialong.x.xie_at_boeing.com>
Date: Wed, 1 Nov 2000 00:07:50 GMT
Message-ID: <G3BKEC.MnF@news.boeing.com>

Thank you for your input.

The problem is that the duration will be different every time the procedure runs.

Also it seems to me that it is not true in my case that "NEXT_DATE are calculated using the value specified in interval and the time the job ENDED". I have run the job for a few days. Each time the job will take a few minutes to 20 minutes ( I have a log to track the duration) to finish but the time shift is about 12 minutes for the past 4 days.

"David Fitzjarrell" <oratune_at_aol.com> wrote in A wrote:
> > 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.

> >
>
> You are accurate about the next time calculation however your solution
> will not work as it returns an error from SQL*Plus:
>
>
> SQL> select to_char(sysdate, 'DD/MM/YYYY:HH24') + 1/12
>   2* from dual
> SQL> /
> ERROR:
> ORA-01722: invalid number
>
>
> no rows selected
>
> As I stated in a previous post the time shift constant will need to be
> slightly less than 1/12 to compensate for the run time of the job.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Received on Tue Oct 31 2000 - 18:07:50 CST