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
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
However, events that
occur while the job is already running are ignored; the
event gets consumed, but does not trigger another run of the
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
2: Allow multiple instances of the new
2: Hold the New job until the previous job
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 (job_name => 'run_load_sales');
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
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
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
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
A new instance of the
job, however, will not be started until the current one
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
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
scheduled jobs are still
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
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
Oracle Training from Don Burleson
The best on site
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
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
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
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
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.