Oracle 11g R2 – Scheduling Tasks

jobsoracleoracle-11g-r2

I have a scheduled task in Oracle 11g scheduled to run at 12:00 and 17:00 each day. If the first run takes more than five hours, what would happen to the second scheduled run? Would it simply be dropped or is it postponed until after the first is complete? If it is possible to do either, how can I check what is used in my DB?

I've seen the task run unscheduled a while after the first completes, but I'm not sure if there are other people starting it manually or if something else is affecting it.

Best Answer

You can easily test it yourself.

Create a job that runs every minute at 0 seconds, but it takes 80 seconds to complete:

begin
  dbms_scheduler.create_job
  (
    job_name => 'TEST_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin dbms_lock.sleep(80); end;',
    enabled => true,
    auto_drop => false,
    repeat_interval => 'FREQ=MINUTELY;BYSECOND=0'
  );
end;
/

After a few minutes:

select log_date, req_start_date, actual_start_date, run_duration
from dba_scheduler_job_run_details where job_name = 'TEST_JOB'
order by log_date;

LOG_DATE            REQ_START_DATE      ACTUAL_START_DATE   RUN_DURATION       
------------------- ------------------- ------------------- -------------------
2018-06-28 20:44:32 2018-06-28 20:42:00 2018-06-28 20:43:12 +00 00:01:20.000000
2018-06-28 20:45:52 2018-06-28 20:44:00 2018-06-28 20:44:32 +00 00:01:20.000000
2018-06-28 20:47:12 2018-06-28 20:45:00 2018-06-28 20:45:52 +00 00:01:20.000000
2018-06-28 20:48:32 2018-06-28 20:46:00 2018-06-28 20:47:12 +00 00:01:20.000000
2018-06-28 20:49:52 2018-06-28 20:48:00 2018-06-28 20:48:32 +00 00:01:20.000000
2018-06-28 20:51:12 2018-06-28 20:49:00 2018-06-28 20:49:52 +00 00:01:20.000000
2018-06-28 20:52:32 2018-06-28 20:50:00 2018-06-28 20:51:12 +00 00:01:20.000000
2018-06-28 20:53:52 2018-06-28 20:52:00 2018-06-28 20:52:32 +00 00:01:20.000000
2018-06-28 20:55:12 2018-06-28 20:53:00 2018-06-28 20:53:52 +00 00:01:20.000000

As you can see from the difference between REQ_START_DATE and ACTUAL_START_DATE (and LOG_DATE), each consequent run waits for the previous one to complete.

This is the simplest configuration, but you can change this behaviour with the max_run_duration parameter that allows you to limit the duration of a job (you need to handle the event that's raised by this).

Or if you used scheduler windows, jobs could be set to automatically stop when the window closes with the stop_on_window_close parameter.

Related Question