Thanks for the question, Yong .

Asked: June 13, 2003 - 1:40 pm UTC

Last updated: July 17, 2003 - 5:27 pm UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

Tom,

I have been using Oracle Job to schedule jobs. One of the problems I have is the schedule keep changing slightly each times. For example, I schedule a job running at 8:00am every day. After few weeks, I check the schedule by select * from dba_jobs, the time become 8:15am or 8:45. I have this problem since Oracle 7,8,9i. How fix the problem?

Another question is how to change next_sec using dbms_job.change?

Thanks,

Yong

and Tom said...

the jobs are run "about when" you ask them to be run.

So, you asked for a job to run at 8am. At 8:01 we ran it and you told us "the next time you should run this will be sysdate+1" -- well, that is 8:01am tomorrow. So tommorrow at about 8:01am (say 8:02) we ran it -- and it slips.

You need to use a NEXT that does NOT slip. You want NEXT to evaluate to 8am TOMORROW, regardless of what time it is right now.

trunc(sysdate+1)+8/24

is tomorrow -- at midnight (trunc) plus 8 hours (8/24 is 8 hours)

that is what your "next" should be, your interval.

Next_sec is the same as next_date, change next_date

ops$tkyte@ORA920> exec dbms_job.submit( :job, 'null;', sysdate, 'trunc(sysdate+1)+8/24' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920> @bigdate

Session altered.

ops$tkyte@ORA920> select job, next_date, next_sec from user_jobs;

JOB NEXT_DATE NEXT_SEC
---------- -------------------- --------
100639 15-jun-2003 08:00:00 08:00:00

ops$tkyte@ORA920> exec dbms_job.change( :job, 'null;', trunc(sysdate+1)+9/24, 'trunc(sysdate+1)+9/24');

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select job, next_date, next_sec from user_jobs;

JOB NEXT_DATE NEXT_SEC
---------- -------------------- --------
100639 15-jun-2003 09:00:00 09:00:00

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 16, 2003 - 12:25 pm UTC

It works. Great! Oracle documentation is not very clean on this.

Thanks,

BTW, normally runing dbms_* package, commit is not necessary. But for dbms_job.*, we have to commit it. It is not consistent with other dbms packages. Any reason for that?

June 17, 2003 - 6:33 am UTC

only those packages that commit say "we commit" - I would say it is the exception to commit -- not the "norm"

question about interupted job

Jerry, July 17, 2003 - 2:32 pm UTC

Tom,

I have a question. I have some database jobs set up to run at night. The server went down at night so that jobs did not run. When the server/db is back up online, the jobs automatically kicked in immediately. Is there any way to prevent the jobs from running other than manually break the job?

Thanks!

July 17, 2003 - 3:47 pm UTC

have the jobs themselves check the time of day. eg instead of scheduling "p;", schedule

if ( to_char(sysdate,'hh24') between 0 and 6 ) then p; end if;

they'll "run" upon startup but not really run.

thanks

Jerry, July 17, 2003 - 5:27 pm UTC

Thanks Tom for the great idea and quick response even in your vacation week. You are great!

Jod schdule time change

dkm, July 25, 2015 - 11:41 am UTC

I hv created a job schedule e.g on 6.00am . But due to some server issue, job schedule is not processed . when server restart,that schedule is processed but time is changed to e.g. 6.00 am to 8.00am.

So my question is that how can i retrive the previous time i.e. 6.00 am because it is already changed.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

question about interupted job

thanks

Jod schdule time change

More to Explore

PL/SQL demos

PL/SQL docs

Oracle Job scheduler is changing time

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

question about interupted job

thanks

Jod schdule time change

More to Explore

PL/SQL demos

PL/SQL docs