Oracle数据库提供了多种方式来实现定时任务的调度和执行，主要包括DBMS_JOB和DBMS_SCHEDULER两种机制。也被称为：定时器/定时作业/作业；数据库定时地自动执行的一些脚本，或做数据备份，或做数据提炼，或做数据库的性能优化，包括重建索引等等的工作，需要用到job。
目录
------------------------------<简单理解与应用>------------------------------
------------------------------<系统理解与应用>------------------------------
------------------------------<简单理解与应用>------------------------------
一、语法
-- 创建
declare
job_1 number;
begin
dbms_job.submit(job=>job_1,--自动生成job_id,
what=>'存过名/要执行的语句;',--需要执行的存储过程或sql语句
next_date=>sysdate,--初次执行的时间
interval=>);--每隔多长时间执行一次
{commit;}--提交
end;
/
-- 查看变化
select * from user_jobs二、简单示例
--向emp2表插入ename
-- 创建
declare
job_1 number;
begin
dbms_job.submit(job=>job_1,--自动生成job_id,
what=>'insert into emp2 (ename)values(''SCOTT'');',--需要执行的存储过程或sql语句
next_date=>sysdate+1/24/60,--初次执行的时间
interval=>'trunc(sysdate,''mi'')+1/24/60');--每隔多长时间执行一次
commit;--提交
end;
/
-- 查看变化
select * from emp2
delete from emp2
select sysdate from dual三、干中学
【1】查看job号
select * from user_jobs;
【2】停止job
begin
dbms_job.broken(25,true);--false开始job
commit;
end;
【3】删除job
begin
dbms_job.remove(24);
commit;
end;
或者：call dbms_job.remove(11)
【4】立即执行job
begin
dbms_job.run(job号);
commit;
end;
或者：call
【注意：】定时job在执行时为了检验是否因为数据错误而陷入每1,2,4,8,16..分钟执行至16次
【避免16次的方法】
先创建一张表a，他的作用是调用job要执行的那个存储过程b，job去调用a
--创建一个存过
--输入员工编号
--把该员工信息插入到emp2里面
--每隔一分钟执行一次
--当job执行失败,它会重试
CREATE OR REPLACE PROCEDURE pro_001(v1 number) IS
BEGIN
INSERT INTO emp2 SELECT * FROM emp WHERE empno=v1;
END;
CALL pro_001(7788)
DECLARE
v1 NUMBER;
BEGIN
dbms_job.submit(job =>v1,
what => 'pro_001(7566);',
next_date => SYSDATE+1/24/60,
INTERVAL => 'TRUNC(SYSDATE,''mi'')+1/24/60');
COMMIT;
END;
1.每次重试时间是递增的，第一次是2分钟,4分钟，8分钟...以此类推；
2.当超过1440分钟,也就是24小时的时候,固定的重试时间为1天
3.超过16次重试后,job会标记为 broken，nexe_date 为 4000-1-1,也就是不在进行job重试
4.oracle数据库重试的时间到达设定的下次执行时间后,以设定的job的执行时间为准
【16次重试的时间大概为7天半】
--为了避免出现重复调用16次真是的存储过程的情况下出现,
--可以用一下方法
CREATE OR REPLACE PROCEDURE pro_002 IS
v_count NUMBER;
BEGIN
INSERT INTO t(ID) VALUES(1); -- 1 1
COMMIT;
SELECT COUNT(1) INTO v_count FROM t;
IF v_count=1 THEN
pro_001();
ELSE
--给自己发邮件
--打印到错误日志停止执行
.....
NULL;
END IF;
END;
/
DECLARE
v1 NUMBER;
BEGIN
dbms_job.submit(job =>v1,
what => 'pro_002();',
next_date => SYSDATE+1/24/60,
INTERVAL => 'TRUNC(SYSDATE,''mi'')+1/24/60');
COMMIT;
END;
/------------------------------<系统理解与应用>------------------------------
一、DBMS_JOB 包
1. 基本概念
DBMS_JOB是Oracle较早提供的作业调度工具，在10g及以前版本广泛使用。
2. 主要功能
提交作业(SUBMIT)
修改作业(CHANGE)
运行作业(RUN)
删除作业(REMOVE)
禁用/启用作业(BROKEN)
3. 创建定时任务
DECLARE
v_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => v_job,
what => 'BEGIN my_procedure; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24' -- 每小时执行一次
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('作业ID: ' || v_job);
END;
/4. 常用时间间隔设置
| 间隔 | 说明 |
| SYSDATE + 1 | 每天执行 |
| SYSDATE + 1/24 | 每小时执行 |
| SYSDATE + 1/(24*60) | 每分钟执行 |
| NEXT_DAY(SYSDATE, ''MONDAY'') | 每周一执行 |
| LAST_DAY(SYSDATE) + 1 | 每月第一天执行 |
| ADD_MONTHS(SYSDATE, 1) | 每月执行 |
5. 管理DBMS_JOB任务
-- 查看所有作业
SELECT job, what, next_date, interval, broken FROM user_jobs;
-- 修改作业
BEGIN
DBMS_JOB.CHANGE(
job => 1,
what => 'BEGIN updated_procedure; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/2' -- 每半小时执行一次
);
COMMIT;
END;
/
-- 立即执行作业
BEGIN
DBMS_JOB.RUN(1);
COMMIT;
END;
/
-- 删除作业
BEGIN
DBMS_JOB.REMOVE(1);
COMMIT;
END;
/
-- 禁用作业
BEGIN
DBMS_JOB.BROKEN(1, TRUE);
COMMIT;
END;
/
-- 启用作业
BEGIN
DBMS_JOB.BROKEN(1, FALSE);
COMMIT;
END;
/二、DBMS_SCHEDULER 包
1. 基本概念
DBMS_SCHEDULER是Oracle 10g引入的更强大的调度器，提供了比DBMS_JOB更丰富的功能。
2. 主要组件
JOB：要执行的任务
PROGRAM：定义执行的操作
SCHEDULE：定义执行时间表
JOB CLASS：作业分类
WINDOW：时间窗口
CHAIN：作业链
3. 创建定时任务
方式1：直接创建作业
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'DAILY_SALES_REPORT',
job_type => 'STORED_PROCEDURE',
job_action => 'sales_pkg.generate_daily_report',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=8', -- 每天8点执行
enabled => TRUE,
comments => '生成每日销售报告'
);
END;
/方式2：使用PROGRAM和SCHEDULE
-- 1. 创建PROGRAM
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'GENERATE_REPORT_PROG',
program_type => 'STORED_PROCEDURE',
program_action => 'sales_pkg.generate_daily_report',
enabled => TRUE
);
END;
/
-- 2. 创建SCHEDULE
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'DAILY_8AM_SCHEDULE',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=8',
comments => '每天上午8点执行'
);
END;
/
-- 3. 创建JOB并关联PROGRAM和SCHEDULE
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'DAILY_SALES_REPORT_JOB',
program_name => 'GENERATE_REPORT_PROG',
schedule_name => 'DAILY_8AM_SCHEDULE',
enabled => TRUE
);
END;
/4. 常用时间间隔语法
DBMS_SCHEDULER使用更丰富的日历语法：
-- 每分钟执行
'FREQ=MINUTELY; INTERVAL=1'
-- 每小时执行
'FREQ=HOURLY; INTERVAL=1'
-- 每天上午8点和下午5点执行
'FREQ=DAILY; BYHOUR=8,17'
-- 每周一至周五上午9点执行
'FREQ=WEEKLY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9'
-- 每月第一天上午10点执行
'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=10'
-- 每季度第一天执行
'FREQ=YEARLY; BYMONTH=JAN,APR,JUL,OCT; BYMONTHDAY=1'5. 管理DBMS_SCHEDULER任务
-- 查看所有作业
SELECT job_name, enabled, state, last_start_date, next_run_date
FROM user_scheduler_jobs;
-- 立即运行作业
BEGIN
DBMS_SCHEDULER.RUN_JOB('DAILY_SALES_REPORT');
END;
/
-- 启用作业
BEGIN
DBMS_SCHEDULER.ENABLE('DAILY_SALES_REPORT');
END;
/
-- 禁用作业
BEGIN
DBMS_SCHEDULER.DISABLE('DAILY_SALES_REPORT');
END;
/
-- 修改作业属性
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'DAILY_SALES_REPORT',
attribute => 'repeat_interval',
value => 'FREQ=DAILY; BYHOUR=7,15'
);
END;
/
-- 删除作业
BEGIN
DBMS_SCHEDULER.DROP_JOB('DAILY_SALES_REPORT');
END;
/三、两种调度机制对比
| 特性 | DBMS_JOB | DBMS_SCHEDULER |
|---|---|---|
| Oracle版本 | 所有版本 | 10g及以上 |
| 功能复杂度 | 简单 | 复杂且功能丰富 |
| 依赖对象 | 无 | 可依赖PROGRAM,SCHEDULE等 |
| 作业链 | 不支持 | 支持 |
| 资源管理 | 有限 | 支持资源计划和窗口 |
| 日志记录 | 有限 | 详细的日志和错误信息 |
| 时间表达式 | 简单日期算法 | 丰富的日历语法 |
| 并发控制 | 有限 | 更好的并发控制 |
四、高级定时任务示例
1. 链式作业(Job Chain)
-- 1. 创建链对象
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN(
chain_name => 'ETL_PROCESS_CHAIN',
comments => '完整的ETL处理流程'
);
END;
/
-- 2. 定义链步骤
BEGIN
-- 步骤1: 数据抽取
DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
chain_name => 'ETL_PROCESS_CHAIN',
step_name => 'EXTRACT_DATA',
program_name => 'ETL_PKG.EXTRACT_DATA'
);
-- 步骤2: 数据转换(依赖步骤1成功)
DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
chain_name => 'ETL_PROCESS_CHAIN',
step_name => 'TRANSFORM_DATA',
program_name => 'ETL_PKG.TRANSFORM_DATA'
);
-- 步骤3: 数据加载(依赖步骤2成功)
DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
chain_name => 'ETL_PROCESS_CHAIN',
step_name => 'LOAD_DATA',
program_name => 'ETL_PKG.LOAD_DATA'
);
-- 定义步骤依赖关系
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'ETL_PROCESS_CHAIN',
condition => 'TRUE',
action => 'START EXTRACT_DATA',
rule_name => 'RULE1'
);
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'ETL_PROCESS_CHAIN',
condition => 'EXTRACT_DATA SUCCEEDED',
action => 'START TRANSFORM_DATA',
rule_name => 'RULE2'
);
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'ETL_PROCESS_CHAIN',
condition => 'TRANSFORM_DATA SUCCEEDED',
action => 'START LOAD_DATA',
rule_name => 'RULE3'
);
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'ETL_PROCESS_CHAIN',
condition => 'LOAD_DATA SUCCEEDED',
action => 'END 0',
rule_name => 'RULE4'
);
END;
/
-- 3. 启用链
BEGIN
DBMS_SCHEDULER.ENABLE('ETL_PROCESS_CHAIN');
END;
/
-- 4. 创建链作业
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'WEEKLY_ETL_JOB',
job_type => 'CHAIN',
job_action => 'ETL_PROCESS_CHAIN',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=2',
enabled => TRUE
);
END;
/2. 使用窗口(Window)和资源计划
-- 1. 创建资源计划
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => 'NIGHTLY_PROCESSING_PLAN',
comment => '夜间批处理资源计划'
);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => 'ETL_GROUP',
comment => 'ETL作业组'
);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'NIGHTLY_PROCESSING_PLAN',
group_or_subplan => 'ETL_GROUP',
comment => 'ETL作业资源分配',
mgmt_p1 => 80
);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'NIGHTLY_PROCESSING_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => '其他作业资源分配',
mgmt_p1 => 20
);
DBMS_RESOURCE_MANAGER.VALIDATE_PLAN('NIGHTLY_PROCESSING_PLAN');
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
-- 2. 创建窗口
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'NIGHTLY_PROCESSING_WINDOW',
resource_plan => 'NIGHTLY_PROCESSING_PLAN',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0',
duration => INTERVAL '8' HOUR,
comments => '夜间批处理窗口'
);
END;
/
-- 3. 创建作业并关联窗口
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'NIGHTLY_ETL_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'ETL_PKG.RUN_FULL_ETL',
schedule_name => 'NIGHTLY_PROCESSING_WINDOW',
enabled => TRUE
);
END;
/五、监控定时任务
1. 查看作业运行状态
-- DBMS_JOB作业状态
SELECT job, what, last_date, last_sec, next_date, next_sec, broken, failures
FROM user_jobs;
-- DBMS_SCHEDULER作业状态
SELECT job_name, state, last_start_date, next_run_date, run_count, failure_count
FROM user_scheduler_jobs;2. 查看作业运行日志
-- DBMS_SCHEDULER作业日志
SELECT job_name, status, log_date, additional_info
FROM user_scheduler_job_run_details
ORDER BY log_date DESC;
-- 查看作业运行错误
SELECT job_name, status, error#, error_msg
FROM user_scheduler_job_run_details
WHERE status = 'FAILED'
ORDER BY log_date DESC;3. 监控长时间运行作业
SELECT sj.job_name, sr.session_id, sr.slave_process_id,
sr.elapsed_time, sr.cpu_used
FROM user_scheduler_running_jobs sr
JOIN user_scheduler_jobs sj ON sr.job_name = sj.job_name;六、最佳实践
合理设置作业间隔：避免过于频繁导致系统负载过高
错误处理：确保作业包含完善的异常处理机制
日志记录：详细记录作业执行情况和结果
资源考虑：大型作业安排在系统低峰期
依赖管理：使用作业链管理有依赖关系的任务
监控告警：设置作业失败通知机制
文档记录：维护作业清单和说明文档
版本控制：对作业相关代码进行版本管理
Oracle定时任务是实现自动化数据处理、报表生成、系统维护等功能的重要工具。根据实际需求选择合适的调度机制(DBMS_JOB或DBMS_SCHEDULER)，并遵循最佳实践，可以构建可靠高效的自动化任务系统。