一、Oracle 定时工作简介
Oracle 定时工作是在 oracle 零碎中一个十分重要的子系统，使用切当，能够大大提高咱们零碎运行和保护能力。oracle 定时工作的性能，能够在指定的工夫点自行执行工作。
那么在理论工作中，什么样的场景会用到定时工作呢？上面是在理论工作中用到的实在业务场景举例
- 在生成环境中，有时候须要在表中记录一些业务日志，零碎运行工夫长了之后，表中日志会越来越多，导致系统性能降落，这时候就须要用到定时工作，定时去删除表中一些工夫年代比拟长远垃圾数据。
- 在某些业务场景中，明细表的数据量特地大，而须要查问明细表一下汇总数据，就须要将明细表中的数据【某天的业务产生的金额、人数等】通过计算汇总到另外表中，这样在查问的时候就能优化查问效率。而以上操作须要在业务量比拟少的状况下进行【个别都在凌晨之后】，这是就须要用到定时工作。
二、dbms_job 波及到的知识点
- 创立 job 脚本
variable jobno number;
dbms_job.submit(:jobno, —-job 号
'your_procedure;', —- 执行的存储过程, ';' 不能省略
next_date, —- 下次执行工夫
'interval' —- 每次间隔时间，interval 以天为单位 );下面是通过脚本创立，当然也能够通过 plsql 图形化工具来创立，具体创立过程如下
零碎会主动调配一个工作号 jobno ，依据 jobno 能够进行如下定时工作操作
- 删除 job: dbms_job.remove(jobno);
- 批改要执行的操作: job:dbms_job.what(jobno, what);
- 批改下次执行工夫：dbms_job.next_date(jobno, next_date);
- 批改间隔时间：dbms_job.interval(jobno, interval);
- 启动 job: dbms_job.run(jobno);
- 进行 job: dbms.broken(jobno, broken, nextdate); –broken 为 boolean 值
三、初始化相干参数 job_queue_processes
- job_queue_process 示意 oracle 可能并发的 job 的数量，当 job_queue_process 值为 0 时示意全副进行 oracle 的 job
- 查看 job_queue_processes 参数
show parameter job_queue_process;
或者
select * from v$parameter where name='job_queue_processes';- 批改 job_queue_processes 参数
alter system set job_queue_processes = 10;四、理论创立一个定时工作（一分钟执行一次），实现定时一分钟往表中插入数据
4.1 创立须要定时插入数据的指标表
create table t_test (id varchar2(30),
name varchar2(30)
);4.2 创立定时执行的存储过程
create or replace procedure proce_t is
begin
insert into t_test
(id, name)
values
('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
commit;
end proce_t;
/4.3 创立定时一分钟定时工作 job
variable jobno number;
begin
dbms_job.submit(:jobno,
'proce_t;',
sysdate,
'sysdate+1/24/60');
commit;
end;4.5 能够依据以下语句查问刚创立好的 job
select job, next_date, next_sec, failures, broken from user_jobs where job = '1424'查问后果如下
SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424'
2 /
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
1424 2020-12-30 13:07:14 0 N其中 broken = N 示意该 job 曾经失效
咱们再来查看指标表中有没有定时插入数据
SQL> select * from t_test;
ID NAME
------------------------------ ------------------------------
1 2020-12-30 13:05:14
1 2020-12-30 13:03:14
1 2020-12-30 13:04:14
1 2020-12-30 13:08:14
1 2020-12-30 13:06:14
1 2020-12-30 13:07:14
6 rows selected能够看到，定时一分钟插入了一条数据。
4.6 如果咱们不须要这个定时工作了，那要怎么进行呢？
1、依据 jobno，执行以下脚本能够进行 job
SQL> begin
2 dbms_job.broken(1424, true, sysdate);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed再来查看定时工作是否停用胜利
SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424';
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
1424 4000-01-01 00:00:00 0 Y咱们发现 BROKEN=Y 阐明定时工作曾经进行胜利了
4.7 启用方才停用的定时工作
SQL> begin
2 dbms_job.run(1424);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed
SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424';
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
1424 2020-12-30 13:20:53 0 NBROKEN = N , 方才的定时工作又启动了
五、定时工作中 job 运行工夫
上面总计了一些定时工作中罕用的运行工夫
- 每分钟执行： TRUNC(sysdate,’mi’) + 1/(24*60)
- 半个小时： sysdate+30/(24*60)
- 每天凌晨 1 点执行：TRUNC(sysdate) + 1 +1/(24)
- 每周一凌晨 1 点执行： TRUNC(next_day(sysdate,’ 星期一 ’))+1/24
- 每月 1 日凌晨 1 点执行： TRUNC(LAST_DAY(SYSDATE))+1+1/24
- 每季度的第一天凌晨 1 点执行： TRUNC(ADD_MONTHS(SYSDATE,3),’Q’) + 1/24
- 每年 7 月 1 日和 1 月 1 日凌晨 1 点： ADD_MONTHS(trunc(sysdate,’yyyy’),6)+1/24
- 每年 1 月 1 日凌晨 1 点执行： ADD_MONTHS(trunc(sysdate,’yyyy’), 12)+1/24
- 每个小时的第 15 分钟运行，比方：8:15，9:15，10:15…：trunc(sysdate,’hh’)+(60+15)/(24*60)