Oracle 系统包 dbms_job 用法 (Oracle定时任务)
用于安排和管理作业队列,通过使用作业,可以使 Oracle 数据库定期执行特定的任务
一、dbms_job 涉及到的知识点
1、创建job
variable jobno number;
dbms_job.submit(
-- job号
:jobno,
-- 执行的存储过程, ';'不能省略
'your_procedure;',
-- 下次执行时间
next_date,
-- 每次间隔时间，interval 以天为单位
'interval'
);
系统会自动分配一个任务号 jobno
2、删除job
dbms_job.remove(jobno);
3、修改要执行的操作job
dbms_job.what(jobno, what);
4、修改下次执行时间
dbms_job.next_date(jobno, next_date);
5、修改间隔时间
dbms_job.interval(jobno, interval);
6、启动 job
dbms_job.run(jobno);
7、停止 job
dbms.broken(jobno, broken, nextdate); –broken为boolean值
二、初始化相关参数 job_queue_processes
1、job_queue_process
表示 Oracle 能够并发的 job 的数量，当 job_queue_process 值为 0 时表示全部停止 Oracle 的 job
2、查看 job_queue_processes 参数
方法一：
show parameter job_queue_process;
方法二：
select * from v$parameter where name='job_queue_processes';
3、修改 job_queue_processes 参数
alter system set job_queue_processes = 10;
三、user_jobs 表结构
| 字段(列) | 类型 | 描述 |
|---|---|---|
| job | number | 任务的唯一标示号 |
| log_user | varchar2(30) | 提交任务的用户 |
| priv_user | varchar2(30) | 赋予任务权限的用户 |
| schema_user | varchar2(30) | 对任务作语法分析的用户模式 |
| last_date | date | 最后一次成功运行任务的时间 |
| last_sec | varchar2(8) | 如 hh24:mm:ss 格式的 last_date 日期的小时，分钟和秒 |
| this_date | date | 正在运行任务的开始时间，如果没有运行任务则为 null |
| this_sec | varchar2(8) | 如 hh24:mm:ss 格式的 this_date 日期的小时，分钟和秒 |
| next_date | date | 下一次定时运行任务的时间 |
四、以下使用一个案例来演示 dbms_job 的使用
1、在 plsql 中创建表
create table t (
id varchar2(30),
name varchar2(30)
);
2、在 plsql 中创建存储过程
create or replace procedure proce_t is
begin
insert into t (id,name) values ('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
commit;
end proce_t;
3、创建 job 任务( 1 分钟执行一次)
在 SQL 后执行
var jobno number;
begin
dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');
commit;
end;
/
4、跟踪任务的情况(查看任务队列)
select job, next_date, next_sec, failures, broken from user_jobs;
说明任务已创建成功。
select * from t;
查看定时任务的结果。可以看出定时任务是正常执行了的
5、停止定时任务
1. 查看定时任务的 job 号
select job, next_date, next_sec, failures, broken from user_jobs;
2. 停止一个已启动的定时任务
begin
dbms_job.broken(1,true, sysdate);
commit;
end;
/
表示停止 job 为 1 的任务。
3. 查看定时任务是否已停止成功
select job, next_date, next_sec, failures, broken from user_jobs;
broken 值为 Y，表示定时任务已停止
6、启动定时任务
1. 查看停止定时任务
select job, next_date, next_sec, failures, broken from user_jobs;
broken 值为 Y，表示定时任务已停止。
2. 启动定时任务
begin
dbms_job.broken(1,false, sysdate);
commit;
end;
/
注意：临时启动一次定时任务如下，它不会更改 broken 为 N
begin
dbms_job.run(1);
commit;
end;
/
3. 查看定时任务是否已启动
select job, next_date, next_sec, failures, broken from user_jobs;
broken 值为 N，表示定时任务启动成功。
7、查看进程数
show parameter job_queue_processes;
必须大于 0，否则执行下面的命令修改：
alter system set job_queue_processes=10;
8、再创建一个任务(每5分钟执行一次)
var jobno number;
begin
--interval是以天为单位的
dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/12');
commit;
end;
/
9、 执行
select job,next_date,next_sec,failures,broken from user_jobs;
结果
总结
关于job运行时间
1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/(24*60)
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24
job的运行频率设置
1.每天固定时间运行
比如早上8:10分钟：Trunc(Sysdate+1) + (8*60+10)/24*60
2.Toad中提供的
每天：trunc(sysdate+1)
每周：trunc(sysdate+7)
每月：trunc(sysdate+30)
每个星期日：next_day(trunc(sysdate),'星期日')
每天6点：trunc(sysdate+1)+6/24
半个小时：sysdate+30/(24 * 60)
3.每个小时的第15分钟运行
比如：8:15，9:15，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60)。