Question:  I scheduled a job which runs every 5 minutes. The execution time for the procedure is not consistent. My question is, if the first job is still running at 6 minutes, will the 2nd copy of the 5 minute job scheduler interval get triggered - Will Oracle dbms_scheduler re-submit a job if the older job is still running?

Answer:  The answer is "it depends" on your parameter settings (event_condition, repeat_interval and parallel_instances).  The docs note that event_condition controls whether a new incarnation of a job is started:

"The Scheduler runs the event-based job for each occurrence of an event that matches event_condition.

However, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job."

There are several option for ensuring that a job does not re-run if the previous incarnation of the job is still executing:

1:  Cancel the new job and do nothing.

2: Allow multiple instances of the new job.

2:  Hold the New job until the previous job has completed.

3:  Hold the previous job for a fixed period of time and try re-execution again.

4; Cancel the running job and start the new job.  Running jobs can be stopped using the dbms_scheduler.stop_job procedure.

begin
dbms_scheduler.stop_job (job_name => 'run_load_sales');
end;
/

Options 3 and 4 could result in conditions where dbms_scheduler has many copies of the same job running at the same time.  In some cases, you may want to code the job such that only a single incarnation of the job is running at any given time.

For complete scripts for this, download the Oracle Script Collection.

There are three general options to take when a dbms_scheduler job executes and a previous instantiation of the same job is still running:

1:  Use repeat_interval:

2:  Use the parallel_instances parameter:

2:  Use a check of v$session to detect the previous job and then sleep and re-submit the job after a fixed time period.

3:  Set a semaphore or use a temporary table to use a a "completion flag" for the job and res-start the job immediately after the previous job completes.

Detecting already-running job with repeat_interval

The repeat_interval feature of dbms_scheduler notes that Oracle controls whether a new dbms_scheduler execution begins if a previous execution of the same job is still running.  The docs note:

"If no value for repeat_interval is specified, the job runs only once at the specified start date.   

 Immediately after a job is started, the repeat_interval is evaluated to determine the next scheduled execution time of the job.

It is possible that the next scheduled execution time arrives while the job is still running.

A new instance of the job, however, will not be started until the current one completes."

Detecting an already running job with parallel_instances

If parallel_instances=true, on the arrival of the specified timed event, the dbms_scheduler creates a new lightweight job to handle that event, so multiple instances of the same event-based job can run in parallel. If parallel_instances=false, then a new event is discarded if it is raised while the job that handles it is already running

Detecting an still-running job with a front-end query

There are options for ensuring at any time, only one job is running. If you set the job to start regardless of whether the previous schedule of the job is still running, just make the first step of the job check to see if the older job is already running. These views will show already scheduled jobs are still running:

• v$session
• dba_scheduler_running_chains
• dba_scheduler_running_jobs
• v$scheduler_running_jobs

You can query v$session for the job name to see if it is still executing and abort on postpone the task (using the sleep command) until it completes.  Better still, query v$scheduler_running_jobs;

select * from v$scheduler_running_jobs where . . . 

You can always use a temporary object (a semaphore, a flat file on /tmp, or a small temporary table) and an indicator that the previous job is still running.

Also, there are tips about this in Dr. Hall's great book "Oracle Job Scheduling"

Oracle Training from Don Burleson  The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:   and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.