Re: time shift in sheduled job

Ift you're on Unix, why don't you just schedule a CRON job to kick off your procedure every 2 Hours.

Keith Jamieson
Phoenix Technology Group

James wrote in message ...
>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 Wed Nov 01 2000 - 03:50:30 CST