dbms_job

[gab]

What happens when you have a scheduled job and the system time jumps beyond that date? The job is still going to be executed or will never execute?

For example
select job, last_date, next_date from user_jobs


JOB LAST_DATE NEXT_DATE
---------- ------------------- -------------------
84 2003/01/07 15:22:11 2003/02/07 00:00:00


and
select sysdate from dual
is 2004/01/01 00:00:00

[sreddy]

I say, It will not. SYSDATE is the base for JOB Scheduler.

[gopi]

I agree with sreddy...please let me add one thing. It is my observation that if the job is running, then SYSDATE will be greater than NEXT date until the job ends. look:


select decode(b.sid,NULL,'Not running',b.sid) sid,
to_char(a.next_date,'MM-DD-YYYY HH24:MI:SS') "NEXT TIME",
to_char(sysdate,'MM-DD-YYYY HH24:MI:SS') "RIGHT NOW",
a.job
from dba_jobs a
,dba_jobs_running b
where log_user = 'STAGE_OWNER'
and a.job = b.job (+)


Notice the job that is currently running - sysdate is greater than next date:

sid.............NEXT TIME...............RIGHT NOW...............job

97..............03-04-2003 13:45:00.....03-04-2003 13:52:03 71
Not running.....03-04-2003 14:01:00.....03-04-2003 13:52:03 30
Not running.....03-04-2003 14:00:00.....03-04-2003 13:52:03 171
Not running.....03-04-2003 14:40:00.....03-04-2003 13:52:03 90

[gab]

I tested and you guys are wrong. As soon as the date jumps, Oracle detects that it has some outstanding jobs and it executes them. The next_date will then be relative to the changed date.

[sreddy]

Interesting!

Curious! why do you wanted test this, situation is not practical as such (I mean having SYSDATE is future date). Unless you are working for NASA or similar and they are using Oracle...

[gab]

We have a time synchronization server that causes lots of problems, it takes the time from somewhere on the internet and aften jumps one year, as oddly as it sounds...

[gopi]

Next date remains in the past until the job is completed. When the job is completed next_date becomes last_date + interval.

you never mention your dB version. im 9.2

good luck, zzzz.........

[slimdave]

Originally posted by gab
We have a time synchronization server that causes lots of problems, it takes the time from somewhere on the internet and aften jumps one year, as oddly as it sounds...

Time to quietly disconnect your servers from it, i think. I can't imagine how being a few seconds off could possibly be as bad as being a whole year off.

[jmodic]

Of course the job will run, no doubt about that. It has been so since DBMS_JOB has been introduced in Oracle7.

Originally posted by sreddy
Interesting!

Curious! why do you wanted test this, situation is not practical as such (I mean having SYSDATE is future date). Unless you are working for NASA or similar and they are using Oracle...

In the context of the original question it is not so unpractical at all. In fact it is very real-life situation. Suppose I schedule the job to execute on 10:00. Now the server goes down at 9:00 (a planned shutdown, a crash, power outage, whatever) and it doesn't start up untill 11:00. So SELECT SYSDATE FROM DUAL after the database is up again will show the time that is way past the job scheduled time. And you don't need to work at NASA to immagine such or simmilar situation.

And of course the job will be executed as soon as the SNP process detects it has some outstanding jobs in the queue.

BTW, this is one of the main differences between the behaviour of DBMS_JOB and the most of OS schedulers.

[sreddy]

Jurij,

I mean to say that looking at his current sysdate which is 2004.