I have a stored PL/SQL procedure which starts every 10 minutes (scheduled job). Sometimes my procedure executes for more than 10 minutes. In this case, Oracle scheduler runs another instance of my procedure in parallel with the first one. How can I prevent Oracle from starting a new procedure until the first one is finished?
I don't see the behavior you report, but again you can post your scheduler code since many options are available.
Setting up a basic test shows that the scheduler will actually skip a scheduled run of a job if the same job is already running. In other words, it will wait until the first instance is complete before launching another (non-parallel).
This example fires an autonomous block that simply sleeps for 90 seconds, but it is scheduled to run every minute:
BEGIN
dbms_scheduler.create_schedule('SCH_MINUTELY', systimestamp,
repeat_interval=>'FREQ=MINUTELY; INTERVAL=1');
--dbms_scheduler.drop_job('JOB_TEST1', false);
--dbms_scheduler.drop_chain('CHAIN_TEST1', false);
--dbms_scheduler.drop_program('PROG_MAIN1', false);
dbms_scheduler.create_program(program_name=>'PROG_MAIN1',program_type=>'PLSQL_BLOCK',
program_action=>'BEGIN
dbms_lock.sleep(90);
END;',
number_of_arguments=>0,enabled=>TRUE,comments=>'Runs for 90 seconds');
-- create chain
dbms_scheduler.create_chain(chain_name=>'CHAIN_TEST1',comments=>'A chain to test scheduler behavior');
-- define chain steps
dbms_scheduler.define_chain_step(chain_name=>'CHAIN_TEST1',step_name=>'STEP_RUN_MAIN',program_name=>'PROG_MAIN1');
-- define chain rules
dbms_scheduler.define_chain_rule(chain_name=>'CHAIN_TEST1',condition=>'TRUE',action=>'START "STEP_RUN_MAIN"',rule_name=>'CHAIN_TEST_R01',comments=>'Run main pgm');
dbms_scheduler.define_chain_rule(chain_name=>'CHAIN_TEST1',condition=>'STEP_RUN_MAIN succeeded',action=>'END',rule_name=>'CHAIN_TEST_R02',comments=>'End of chain');
-- enable chain
dbms_scheduler.ENABLE ('CHAIN_TEST1');
-- create job
dbms_scheduler.create_job(job_name=>'JOB_TEST1',job_type=>'CHAIN',job_action=>'CHAIN_TEST1',schedule_name=>'SCH_MINUTELY',enabled=>TRUE,auto_drop=>FALSE,comments=>'Job to test scheduler');
END;
Looking at the scheduler logs (dba_scheduler_job_run_details):
LOG_DATE JOB_NAME JOB_SUBNAME STATUS ACTUAL_START_DATE RUN_DURATION
-------------------------------------- ----------------------------------------------------------------- ----------------------------------------------------------------- ------------------------------ -------------------------------------- ------------
23-SEP-13 10.49.34.332727000 AM -04:00 JOB_TEST1 SUCCEEDED 23-SEP-13 10.48.04.206153000 AM -04:00 0 0:1:30.0
23-SEP-13 10.49.34.313332000 AM -04:00 JOB_TEST1 STEP_RUN_MAIN SUCCEEDED 23-SEP-13 10.48.04.302311000 AM -04:00 0 0:1:30.0
23-SEP-13 10.47.34.231511000 AM -04:00 JOB_TEST1 SUCCEEDED 23-SEP-13 10.46.04.105827000 AM -04:00 0 0:1:30.0
23-SEP-13 10.47.34.212905000 AM -04:00 JOB_TEST1 STEP_RUN_MAIN SUCCEEDED 23-SEP-13 10.46.04.200956000 AM -04:00 0 0:1:30.0
23-SEP-13 10.45.34.144779000 AM -04:00 JOB_TEST1 SUCCEEDED 23-SEP-13 10.44.04.011605000 AM -04:00 0 0:1:30.0
23-SEP-13 10.45.34.124745000 AM -04:00 JOB_TEST1 STEP_RUN_MAIN SUCCEEDED 23-SEP-13 10.44.04.113662000 AM -04:00 0 0:1:30.0
23-SEP-13 10.43.35.048820000 AM -04:00 JOB_TEST1 SUCCEEDED 23-SEP-13 10.42.04.906209000 AM -04:00 0 0:1:30.0
23-SEP-13 10.43.35.024348000 AM -04:00 JOB_TEST1 STEP_RUN_MAIN SUCCEEDED 23-SEP-13 10.42.05.012929000 AM -04:00 0 0:1:30.0
23-SEP-13 10.41.34.950533000 AM -04:00 JOB_TEST1 SUCCEEDED 23-SEP-13 10.40.04.803192000 AM -04:00 0 0:1:30.0
23-SEP-13 10.41.34.918242000 AM -04:00 JOB_TEST1 STEP_RUN_MAIN SUCCEEDED 23-SEP-13 10.40.04.900061000 AM -04:00 0 0:1:30.0
10 rows selected
We can see that while the Scheduler fires this job every minute, the job itself runs for 1.5 minutes, so:
time0: scheduler runs job
time1: scheduler sees job is still running, does nothing
time2: scheduler sees this job isnt running, runs job
time3: scheduler sees job is still running, does nothing
time4: scheduler sees this job isnt running, runs job
etc, etc