-
Hi,
I would like to run job every five minutes(e.g. 00:05, 00:10, 00:15,.....) using DBMS_JOB. I set next_date parameter to
trunc(sysdate)+1 for tomorrow midnight.
For interval I can user sysdate+5(24*60). But, it will
change every time job runs. Is there a way to get exact
5 minute interval?
thanks for your help
np70
-
Code:
select to_char(trunc(sysdate,'mi')+1/288, 'dd-mon-yyyy hh24:mi:ss') from dual
/
-
Pando,
thank you for reply. Could you tell me what 1/288 is for?
Also, should I just put
to_char(trunc(sysdate,'mi')+1/288, 'dd-mon-yyyy hh24:mi:ss')
as interval parameter of dbms_job.submit procedure?
thanks,
np70
-
Pando,
I figured what 1/288 is for. It is 5/(24*60). But
this value is valid only if we assume job finished within
minute. If job takes more than One minute, next schedule
time will be incorrect.
e.g.
00:00 --> first Job (took 30 seconeds)
00:05 --> second job ( took 1 minute and 30 seconds).
so next time will be
00:11 -->third job
Is there a way to get next interval without using current
time?
thanks
Np70
-
it should execute every 5 minutes because next_date in user_jobs is set once the job starts not when the job is finished
I can test it because I dont have a job that takes longer than 1 minute
[Edited by pando on 09-25-2001 at 12:36 PM]
-
it works, I tried it
Code:
connect internal
grant execute on dbms_lock to lsc:
connect lsc
create or replace procedure sleep
as
begin
dbms_lock.sleep(90);
end;
/
variable job number
begin
dbms_job.submit(:job, 'sleep;', sysdate, 'trunc(sysdate,''mi'')+1/288');
end;
/
commit;
select next_date from user_jobs;
next_date
------------
08:14:02
select sysdate from dual;
08:14:23
exec dbms_job.run(4);
sleeps 90 seconds............
select next_date from user_jobs;
next_date
-----------
08:19:00
wait for 5 minutes for next execution
08:19:00 job starts
sleeps 90 seconds............
select next_date from user_jobs;
next_date
-----------
08:24:00
it is scheduled for every 5 minutes even the jobs takes longer than a minute
-
pando,
thank you. now i can see that next interval is calculated
from start time and not the end time.
again thanks
np70
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|