The idea is that when a task finishes, it waits two minutes and runs again.
The problem is that the duration of the task is variable and the rescheduling must be done as soon as it finishes. Whatever time it is, it will be executed two minutes later.
To do this, managing the execution interval to calculate the next time is not a good idea, because the value of the next execution is determined at the time the job starts running, so to create this chain of serial executions we must modify the START_DATE.
I am going to create a procedure called “SLEEP_TIME” (sleep_time), in which the process will sleep for a variable number of seconds between zero and three minutes. To record the start and end of the procedure, record the timestamp in an EXECUTION table for the values of START TO SLEEP (sleep runs) and WAKES UP (sleep ends).
create table execution_times (estado varchar2(20),tiempo timestamp);
create or replace procedure sleep_time is
begin
-- log in execution_times table when it starts to sleep
insert into execution_times values ('START TO SLEEP',systimestamp);
commit;
-- the process sleeps for a random time between 0 seconds and 3 minutes
dbms_session.sleep(mod(abs(dbms_random.random),180));
-- log in execution_times table when it finishes sleeping and wakes up
insert into execution_times values ('WAKES UP',systimestamp);
commit;
end;
/
So that each execution leaves this log recorded.
SQL> exec sleep_time; Procedimiento PL/SQL terminado correctamente. Transcurrido: 00:01:19.02 SQL> select * from execution_times; ESTADO -------------------- TIEMPO --------------------------------------------------------------------------- START TO SLEEP 28/11/24 07:56:23,964952 WAKES UP 28/11/24 07:57:42,979512
To schedule its execution, I’m going to create a job called SLEEP_AND_WAKE to start as soon as it is enabled and immediately after that execute SLEEP_TIME at the start of the minute.
The interval frequency is therefore executions every minute, when the second zero coincides. This is something additional, to make the processes always start after two minutes of the end of their execution but at the beginning of the following minute. I do this only to see the precise moments of job start that coincide with an exact minute.
If we want to be faithful to the exact execution after two minutes of finishing the previous execution, we would then remove the parameter “bysecond=0;”.
begin
dbms_scheduler.create_job (
job_name => 'sleep_and_wake',
job_type => 'plsql_block',
job_action => 'begin sleep_time; end;',
start_date => systimestamp,
repeat_interval => 'freq=minutely; bysecond=0;',
enabled => false);
end;
/
PL/SQL procedure successfully completed.
Now the job is created, but it is not active. This is because before activating it, we must modify the procedure so that, when the execution is finished, it modifies the SLEEP_AND_WAKE_UP job so that it starts two minutes later.
Exactly two minutes later, when it reaches the second “00”. That is, when the next minute starts.
create or replace procedure sleep_time is
begin
-- log in execution_times table when it starts to sleep
insert into execution_times values ('START TO SLEEP',systimestamp);
commit;
-- the process sleeps for a random time between 0 seconds and 3 minutes
dbms_session.sleep(mod(abs(dbms_random.random),180));
-- log in execution_times table when it finishes sleeping and wakes up
insert into execution_times values ('WAKES UP',systimestamp);
commit;
-- NEW PART we set up again the start_time of the main job.
dbms_scheduler.set_attribute (
name => 'sleep_and_wake',
attribute => 'start_date',
value => sysdate+(2/24/60)); --> two minutes ahead
end;
/
At this point we can enable the job, and it will be executed instantly, and each subsequent execution will be rescheduled on the fly regardless of the time it takes.
Jobs are now chained and being executed serially with a delay of 2 minutes each, starting at second “00” of next minute.
SQL> select * from execution_times; ESTADO TIEMPO -------------------- --------------------------------------------------------------------------- START TO SLEEP 28-NOV-24 11.15.01.001800 AM WAKES UP 28-NOV-24 11.16.39.064239 AM --> 1 minute and 38 seconds later START TO SLEEP 28-NOV-24 11.19.00.185823 AM --> 2 minutes and 21 seconds later (11:19 exact time) WAKES UP 28-NOV-24 11.19.41.334749 AM --> 41 seconds later START TO SLEEP 28-NOV-24 11.22.00.129578 AM --> 2 minutes and 19 seconds later (11:22 exact time) WAKES UP 28-NOV-24 11.22.10.134432 AM --> ...and so on. START TO SLEEP 28-NOV-24 11.25.00.075948 AM WAKES UP 28-NOV-24 11.25.45.366549 AM START TO SLEEP 28-NOV-24 11.28.00.024666 AM WAKES UP 28-NOV-24 11.30.57.174717 AM START TO SLEEP 28-NOV-24 11.33.00.029588 AM WAKES UP 28-NOV-24 11.33.29.240348 AM START TO SLEEP 28-NOV-24 11.36.00.199082 AM WAKES UP 28-NOV-24 11.37.53.430395 AM