dbms_job.interval

[Chintz]

I need to change the interval setting for a job defined in dbms_job. I need for the job to run everyday at 7.00 AM. WHat is the correct interval syntax for it. Is it
"dbms_job.interval(,'TRUNC(SYSDATE+1)+7/24');"

APpreciate the input in advance.

Thanks,
Chintz

[jmodic]

Yes, your syntax is correct, except that the job# as the first argument is missing in a call.

dbms_job.interval(123,'TRUNC(SYSDATE+1)+7/24');

where "123" is the number of your job (job id). Query view USER_JOBS to get your proper job id.

[Chintz]

I missed the job number in my initial posting. But, yes, I do understand that I need to put the job# there. But when I execute that command and query the dba_jobs table for that job the next_sec field is set at 00:00:00, whereas it should be 7:00:00.
So, it makes me feel there is something wrong with the syntax, 'cause the system should calculate and display the next time the job needs to be run.

Chintz

[jmodic]

Have you issued a COMMIT after the call to DBMS_JOB.INTERVAL()? If you are querying your user_jobs from another session, you won't see the changes untill you commit them. And of course, the changes will also not be visible to the dbms_job scheduler (ie will not take effect) untill you commit them.

[Chintz]

Yes, I did do a commit.

Chintz

[gpsingh]

Hi,

The interval is the interval between the job run and next run time.
You cannot set the time it should run but after how much time it should run after job execution.
So if you want it to run at 7AM ,u have to execute it at 7AM
and then give trunc(sysdate) + 1.

I hope it is clear.

[Chintz]

There must be a way of running a job at a certain time. If I wanted to set up the job to run at 7.00 AM everyday how would I do it?

Chintz

[gpsingh]

Actually your syntax should have worked,
Try two things:

stop job queue processes and restart them.

Or else remove old job and resubmit with new interval.

Hope it helps.

[Chintz]

How do you stop the job process?
Also, should I just do a dbms_job.remove & then a dbms_job.submit?

Chintz

[jmodic]

Originally posted by gpsingh
Hi,

The interval is the interval between the job run and next run time.
You cannot set the time it should run but after how much time it should run after job execution.
So if you want it to run at 7AM ,u have to execute it at 7AM
and then give trunc(sysdate) + 1.

I hope it is clear.

You are wrong. If you set the job to run today at 7AM and set the interval to 'trunc(sysdate) + 1', your job will run at midnight every day, not at 7AM. You could set the interval to 'sysdate+1', which means it will run tomorow and any other day at *approximately* 7AM. Why approximately? Today it might happen to execute 30 seconds past 7AM. So when the job finishes today, tje next execution date will be set at tomorow at 07:00:30AM. Again, tomorow the job scheduler might be late for another 30 seconds, resulting at execution time at 07:01:00AM. And so on and on, the start time of the job will slowlly be shifted and in a couple of months you'll find it starting at 8AM instead of at 7AM.

To avoid this, you have to "anchore" the next starting time of the job, which is exactly what the chintz's original call does: "dbms_job.interval(xyz,'TRUNC(SYSDATE+1)+7/24');" This ensures that the job will be executed at 7AM every day. It might actualy start a couple of secunds later (depending on your JOB_QUEUE_INTERVAL setting), but the the time offset will not accumulate!

Chintz, again, your syntax is perfectly correct, I'm 100% sure. You say you are seeing NEXT_SEC field as set to 00:00:00. But this is normal! You have only set the new interval, not the new time of your first next execution time. To change your next execution time, you must use DBMS_JOB.NEXT_DATE(xyz,TRUNC(SYSDATE)+14/24); if you want to execute it today at 2PM for example. But tomorow it will be run at 7AM!