oracle - 一个 Oracle 计划作业与另一个实例并行运行。如何防止这种情况？

Question

我有一个存储的 PL/SQL 过程，每 10 分钟启动一次(计划作业)。有时我的程序执行超过 10 分钟。在这种情况下，Oracle 调度程序与第一个并行运行我的过程的另一个实例。我怎样才能阻止 Oracle 在第一个过程完成之前开始一个新过程？

Answer 1

我没有看到您报告的行为，但是您可以再次发布您的调度程序代码，因为有许多选项可用。

设置一个基本测试表明，如果同一个作业已经在运行，调度程序实际上会跳过作业的预定运行。换句话说，它将等到第一个实例完成后再启动另一个实例(非并行)。

这个例子触发了一个自主 block ，它只休眠 90 秒，但它计划每分钟运行一次:

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;

查看调度程序日志 (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 

我们可以看到，虽然调度程序每分钟都会触发此作业，但作业本身会运行 1.5 分钟，因此:

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

等等等等