Run Job every five minutes

[np70]

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

[pando]

select to_char(trunc(sysdate,'mi')+1/288, 'dd-mon-yyyy hh24:mi:ss') from dual
/

[np70]

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

[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

[pando]

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]

[pando]

it works, I tried it

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

[np70]

pando,
thank you. now i can see that next interval is calculated
from start time and not the end time.

again thanks

np70