Oracle 调度器（DBMS_SCHEDULER）

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

DBMS_SCHEDULER 用于调度和执行任务[1]：

优势（vs DBMS_JOB）：

更强大
基于 PL/SQL 块、过程、脚本
调度灵活
事件驱动
链式任务

2. 创建作业

2.1 简单作业

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'my_job',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN my_proc; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE
  );
END;
/

2.2 job_type

类型	说明
PLSQL_BLOCK	PL/SQL 块
STORED_PROCEDURE	存储过程
EXECUTABLE	外部脚本
CHAIN	链

2.3 repeat_interval

FREQ=YEARLY|MONTHLY|WEEKLY|DAILY|HOURLY|MINUTELY|SECONDLY
INTERVAL=N
BYHOUR=HOUR
BYMINUTE=MINUTE
BYSECOND=SECOND
BYDAY=MON|TUE|...
BYDATE=YYYYMMDD

示例：

FREQ=DAILY; BYHOUR=2                    -- 每天 2 点
FREQ=WEEKLY; BYDAY=MON,FRI              -- 每周一、五
FREQ=MONTHLY; BYMONTHDAY=1              -- 每月 1 号
FREQ=HOURLY; INTERVAL=2                 -- 每 2 小时
FREQ=MINUTELY; INTERVAL=15              -- 每 15 分钟
FREQ=DAILY; BYHOUR=9; BYMINUTE=30       -- 每天 9:30

3. 管理作业

3.1 启用/禁用

EXEC DBMS_SCHEDULER.ENABLE('my_job');
EXEC DBMS_SCHEDULER.DISABLE('my_job');

3.2 立即运行

EXEC DBMS_SCHEDULER.RUN_JOB('my_job');

3.3 停止

EXEC DBMS_SCHEDULER.STOP_JOB('my_job');

3.4 删除

EXEC DBMS_SCHEDULER.DROP_JOB('my_job');

3.5 修改

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE(
  name => 'my_job',
  attribute => 'repeat_interval',
  value => 'FREQ=DAILY; BYHOUR=3'
);

4. 程序（Program）

4.1 创建程序

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'my_program',
    program_type => 'STORED_PROCEDURE',
    program_action => 'my_proc',
    number_of_arguments => 1,
    enabled => TRUE
  );
END;
/

4.2 定义参数

BEGIN
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name => 'my_program',
    argument_position => 1,
    argument_name => 'dept_id',
    argument_type => 'NUMBER',
    default_value => 10
  );
END;
/

4.3 使用程序

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'my_job',
    program_name => 'my_program',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY',
    enabled => TRUE
  );
END;
/

5. 调度（Schedule）

5.1 创建调度

BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'nightly_schedule',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    comments => 'Nightly at 2 AM'
  );
END;
/

5.2 使用调度

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'my_job',
    program_name => 'my_program',
    schedule_name => 'nightly_schedule',
    enabled => TRUE
  );
END;
/

6. 作业链（Chain）

6.1 创建链

BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN(
    chain_name => 'my_chain'
  );
END;
/

6.2 定义步骤

BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'my_chain',
    step_name => 'step1',
    program_name => 'program1'
  );
  
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'my_chain',
    step_name => 'step2',
    program_name => 'program2'
  );
END;
/

6.3 定义规则

BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'my_chain',
    condition => 'TRUE',
    action => 'START step1'
  );
  
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'my_chain',
    condition => 'step1 COMPLETED',
    action => 'START step2'
  );
  
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'my_chain',
    condition => 'step2 COMPLETED',
    action => 'END'
  );
END;
/

6.4 启用链

EXEC DBMS_SCHEDULER.ENABLE('my_chain');

-- 创建链作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'chain_job',
    job_type => 'CHAIN',
    job_action => 'my_chain',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY',
    enabled => TRUE
  );
END;
/

7. 事件驱动

7.1 创建事件队列

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'event_job',
    program_name => 'my_program',
    event_condition => 'tab.user_data.event_type = ''START_JOB''',
    queue_spec => 'my_queue',
    enabled => TRUE
  );
END;
/

8. 查看作业

8.1 作业列表

SELECT job_name, enabled, state, last_start_date, next_run_date
FROM user_scheduler_jobs;

8.2 运行历史

SELECT job_name, status, run_duration, actual_start_date
FROM user_scheduler_job_run_details
ORDER BY actual_start_date DESC;

8.3 日志

SELECT * FROM user_scheduler_job_log
ORDER BY log_date DESC;

9. 外部作业

9.1 创建

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'shell_job',
    job_type => 'EXECUTABLE',
    job_action => '/u01/scripts/myscript.sh',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE
  );
END;
/

9.2 凭证

BEGIN
  DBMS_SCHEDULER.CREATE_CREDENTIAL(
    credential_name => 'my_cred',
    username => 'oracle',
    password =****** 'password'
  );
END;
/

-- 使用
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('shell_job', 'credential_name', 'my_cred');

10. 常见坑与排错

10.1 作业不运行

-- 1. 检查 enabled
SELECT job_name, enabled FROM user_scheduler_jobs;
-- 2. 检查 next_run_date
-- 3. 检查调度

10.2 ORA-27369: 外部作业失败

-- 检查脚本权限
-- 检查凭证

10.3 作业失败

-- 查看错误
SELECT job_name, status, additional_info
FROM user_scheduler_job_run_details
WHERE status = 'FAILED';

10.4 作业卡住

-- 停止
EXEC DBMS_SCHEDULER.STOP_JOB('job_name', force => TRUE);

11. 最佳实践

模块化设计：Program + Schedule + Job
复用调度：统一管理
错误处理：作业内捕获
日志记录：便于排查
链式任务：复杂流程
事件驱动：实时响应
凭证安全：外部作业
监控运行：定期检查
资源管理：避免冲突
测试充分：验证逻辑

12. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “DBMS_SCHEDULER” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/scheduling-jobs.html