使用DBMS_SCHEDULER限制PL/SQL块执行时间遇异常求助

根源说明 #

你遇到的日志顺序问题，核心原因是DBMS_SCHEDULER是异步调度机制：当你在当前会话创建并启用job后，Oracle不会立刻执行job，而是由后台调度进程（如cjq0）负责启动任务，这个调度过程存在短暂延迟。所以你的主会话会先进入循环查询，此时job还未被调度启动，自然在user_scheduler_running_jobs中查不到记录，直到后台调度完成后job才开始执行。

解决办法 #

方案一：创建job后添加短暂等待

在进入监控循环前，给后台调度留足启动时间，比如添加1秒的sleep：

declare 
  sJob varchar2(100) := 'limit_test1';
  bRunning number;
begin
  log('create job');
  dbms_scheduler.create_job(sJob, 'PLSQL_BLOCK', 
    'begin log(''start job''); dbms_lock.sleep(5); log(''finish job''); end;', 
    start_date => systimestamp, enabled => true);
  
  -- 新增：等待调度器启动job，时长可根据实际情况调整
  dbms_lock.sleep(1);
  
  log('start loop');
  loop
    bRunning := 0;
    for rec in (select 1 from user_scheduler_running_jobs t where t.job_name = sJob) loop
      bRunning := 1;
    end loop;
    log('bRunning = '||bRunning);
    exit when bRunning = 0; -- 可在此处添加超时逻辑，比如循环次数限制
    dbms_lock.sleep(1);
  end loop;
  
  -- 清理测试用job，避免残留
  dbms_scheduler.drop_job(sJob);
end;

方案二：结合job状态视图增强判断

仅依赖user_scheduler_running_jobs可能存在视图刷新延迟，可同时查询user_scheduler_jobs的state字段，判断job是否处于调度中或运行状态：

declare 
  sJob varchar2(100) := 'limit_test1';
  bRunning number;
  vJobState varchar2(30);
begin
  log('create job');
  dbms_scheduler.create_job(sJob, 'PLSQL_BLOCK', 
    'begin log(''start job''); dbms_lock.sleep(5); log(''finish job''); end;', 
    start_date => systimestamp, enabled => true);
  
  dbms_lock.sleep(0.5);
  log('start loop');
  loop
    bRunning := 0;
    -- 先查运行中的job
    for rec in (select 1 from user_scheduler_running_jobs t where t.job_name = sJob) loop
      bRunning := 1;
    end loop;
    -- 如果没查到，再检查job状态是否为调度中/运行中
    if bRunning = 0 then
      select state into vJobState from user_scheduler_jobs where job_name = sJob;
      if vJobState in ('RUNNING', 'SCHEDULED') then
        bRunning := 1;
      end if;
    end if;
    
    log('bRunning = '||bRunning);
    exit when bRunning = 0;
    dbms_lock.sleep(1);
  end loop;
  
  dbms_scheduler.drop_job(sJob);
end;

补充提示 #

• 测试完成后记得调用dbms_scheduler.drop_job清理job，避免在数据库中残留无效任务；
• 生产环境中如果需要严格的超时控制，可结合DBMS_SCHEDULER的max_run_duration属性（创建job时指定），让Oracle自动终止超时的job，无需手动监控。

内容的提问来源于stack exchange，提问作者Ayb