一、Oracle定時(shí)任務(wù)簡介
Oracle定時(shí)任務(wù)是在oracle系統(tǒng)中一個(gè)非常重要的子系統(tǒng)，運(yùn)用得當(dāng)，可以大大提高我們系統(tǒng)運(yùn)行和維護(hù)能力。oracle定時(shí)任務(wù)的功能，可以在指定的時(shí)間點(diǎn)自行執(zhí)行任務(wù)。
那么在實(shí)際工作中，什么樣的場景會(huì)用到定時(shí)任務(wù)呢？下面是在實(shí)際工作中用到的真實(shí)業(yè)務(wù)場景舉例
- 在生成環(huán)境中，有時(shí)候需要在表中記錄一些業(yè)務(wù)日志，系統(tǒng)運(yùn)行時(shí)間長了之后，表中日志會(huì)越來越多，導(dǎo)致系統(tǒng)性能下降，這時(shí)候就需要用到定時(shí)任務(wù)，定時(shí)去刪除表中一些時(shí)間年代比較久遠(yuǎn)垃圾數(shù)據(jù)。
- 在某些業(yè)務(wù)場景中，明細(xì)表的數(shù)據(jù)量特別大，而需要查詢明細(xì)表一下匯總數(shù)據(jù)，就需要將明細(xì)表中的數(shù)據(jù)【某天的業(yè)務(wù)產(chǎn)生的金額、人數(shù)等】通過計(jì)算匯總到另外表中，這樣在查詢的時(shí)候就能優(yōu)化查詢效率。而以上操作需要在業(yè)務(wù)量比較少的情況下進(jìn)行【一般都在凌晨之后】，這時(shí)就需要用到定時(shí)任務(wù)。
二、dbms_job涉及到的知識(shí)點(diǎn)
- 創(chuàng)建job腳本
variable jobno number;
dbms_job.submit(:jobno, —-job號(hào)
'your_procedure;', —-執(zhí)行的存儲(chǔ)過程, ';'不能省略
next_date, —-下次執(zhí)行時(shí)間
'interval' —-每次間隔時(shí)間，
interval 以天為單位);
上面是通過腳本創(chuàng)建，當(dāng)然也可以通過plsql圖形化工具來創(chuàng)建，具體創(chuàng)建過程如下
**系統(tǒng)會(huì)自動(dòng)分配一個(gè)任務(wù)號(hào)jobno ** ，根據(jù)jobno 可以進(jìn)行如下定時(shí)任務(wù)操作
- 刪除job: dbms_job.remove(jobno);
- 修改要執(zhí)行的操作: job:dbms_job.what(jobno, what);
- 修改下次執(zhí)行時(shí)間：dbms_job.next_date(jobno, next_date);
- 修改間隔時(shí)間：dbms_job.interval(jobno, interval);
- 啟動(dòng)job: dbms_job.run(jobno);
- 停止job: dbms.broken(jobno, broken, nextdate); –broken為boolean值
三、初始化相關(guān)參數(shù)job_queue_processes
- job_queue_process表示oracle能夠并發(fā)的job的數(shù)量，當(dāng)job_queue_process值為0時(shí)表示全部停止oracle的job
- 查看job_queue_processes參數(shù)
show parameter job_queue_process;
或者
select * from v$parameter where name='job_queue_processes';
- 修改job_queue_processes參數(shù)
alter system set job_queue_processes = 10;
四、實(shí)際創(chuàng)建一個(gè)定時(shí)任務(wù)（一分鐘執(zhí)行一次），實(shí)現(xiàn)定時(shí)一分鐘往表中插入數(shù)據(jù)
4.1 創(chuàng)建需要定時(shí)插入數(shù)據(jù)的目標(biāo)表
create table t_test (id varchar2(30),
name varchar2(30)
);
4.2 創(chuàng)建定時(shí)執(zhí)行的存儲(chǔ)過程
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 創(chuàng)建定時(shí)一分鐘定時(shí)任務(wù)job
variable jobno number;
begin
dbms_job.submit(:jobno,
'proce_t;',
sysdate,
'sysdate+1/24/60');
commit;
end;
4.5 可以根據(jù)以下語句查詢剛創(chuàng)建好的job
select job, next_date, next_sec, fAIlures, broken from user_jobs where job = '1424'
查詢結(jié)果如下
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已經(jīng)生效
我們再來查看目標(biāo)表中有沒有定時(shí)插入數(shù)據(jù)
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
可以看到，定時(shí)一分鐘插入了一條數(shù)據(jù)。
4.6 如果我們不需要這個(gè)定時(shí)任務(wù)了，那要怎么停止呢？
1、根據(jù)jobno，執(zhí)行以下腳本可以停止job
SQL> begin
2 dbms_job.broken(1424, true, sysdate);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed
再來查看定時(shí)任務(wù)是否停用成功
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
我們發(fā)現(xiàn)BROKEN=Y 說明定時(shí)任務(wù)已經(jīng)停止成功了
4.7 啟用剛才停用的定時(shí)任務(wù)
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 N
BROKEN = N ,剛才的定時(shí)任務(wù)又啟動(dòng)了
五、定時(shí)任務(wù)中job運(yùn)行時(shí)間
下面總計(jì)了一些定時(shí)任務(wù)中常用的運(yùn)行時(shí)間
- 每分鐘執(zhí)行： TRUNC(sysdate,'mi') + 1/(24*60)
- 半個(gè)小時(shí)： sysdate+30/(24*60)
- 每天凌晨1點(diǎn)執(zhí)行：TRUNC(sysdate) + 1 +1/(24)
- 每周一凌晨1點(diǎn)執(zhí)行： TRUNC(next_day(sysdate,'星期一'))+1/24
- 每月1日凌晨1點(diǎn)執(zhí)行： TRUNC(LAST_DAY(SYSDATE))+1+1/24
- 每季度的第一天凌晨1點(diǎn)執(zhí)行： TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
- 每年7月1日和1月1日凌晨1點(diǎn)： ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
- 每年1月1日凌晨1點(diǎn)執(zhí)行： ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24
- 每個(gè)小時(shí)的第15分鐘運(yùn)行，比如：8:15，9:15，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60)
