Oracle中DBMS_JOB建立定時任務__Oracle

用於安排和管理作業隊列,通過使用作業,可以使ORACLE資料庫定期執行特定的任務。

一、dbms_job涉及到的知識點
1、建立job：
variable jobno number;
dbms_job.submit(:jobno,—-job號  
               '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的使用

一、在plsql中建立表：

1 2 3 4

create table t(    id  varchar2(30),    name varchar2(30) );

二、在plsql中建立預存程序：

1 2 3 4 5 6

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; /

三、建立job任務(1分鐘執行一次)：
在sql>後執行：

1 2 3 4 5 6

variable jobno number; begin     dbms_job.submit(:jobno, 'proce_t;' ,sysdate, 'sysdate+1/24/60' );     commit; end; /

提交後提示：

1 2 3 4

pl/sql procedure successfully completed jobno --------- 25

四、跟蹤任務的情況(查看任務隊列)：

1 2 3 4 5

sql> select job, next_date, next_sec, failures,broken from user_jobs;            jobnext_date  next_sec          failures broken ---------- ----------- ---------------- ----------------          252012/9/14 110:59:46                 0 n

說明任務已建立成功。
執行select * from t;查看定時任務的結果。可以看出定時任務是正常執行了的。
    
五、停止定時任務
1、查看定時任務的job號。

1 2 3 4 5

sql> select job, next_date, next_sec, failures,broken from user_jobs;            jobnext_date  next_sec          failures broken ---------- ----------- ---------------- ----------------          252012/9/14 111:01:48                 0 n

2、停止一個已啟動的定時任務：

1 2 3 4 5

begin     dbms_job.broken(25, true ,sysdate);     commit ; end ; /

表示停止job為25的任務。

執行後顯示如下：
pl/sql procedure successfully completed

3、查看定時任務是否已停止成功

1 2 3 4 5

sql> select job, next_date, next_sec, failures,broken from user_jobs;            jobnext_date  next_sec          failures broken ---------- ----------- ---------------- ----------------          254000/1/1   00:00:00                 0 y

broken值為y，表示定時任務已停止。

六、啟動定時任務
1、查看停止定時任務

1 2 3 4 5

sql> select job, next_date, next_sec, failures,broken from user_jobs;            jobnext_date  next_sec          failures broken ---------- ----------- ---------------- ----------------          254000/1/1   00:00:00                 0 y

broken值為y，表示定時任務已停止。

2、啟動定時任務

1 2 3 4 5

begin     dbms_job.run(25);     commit ; end ; /

3、查看定時任務是否已啟動

1 2 3 4 5

sql> select job, next_date, next_sec, failures,broken from user_jobs;            jobnext_date  next_sec          failures broken ---------- ----------- ---------------- ----------------          25 2012 / 9 / 14 1 11 : 06 : 17                  0 n

broken值為n，表示定時任務啟動成功。

七、查看進程數
show parameter job_queue_processes;
必須大於0，否則執行下面的命令修改：
alter system set job_queue_processes=10;
 
八、再建立一個任務(每5分鐘執行一次)：

1 2 3 4 5 6

variable jobno number; begin     dbms_job.submit(:jobno, 'proce_t;' ,sysdate, 'sysdate+1/24/12' ); --interval是以天為單位的     commit ; end ; /

九、 執行selectjob,next_date,next_sec,failures,broken from user_jobs;
結果：

1 2 3 4 5 6

sql> select job,next_date,next_sec,failures,brokenfrom user_jobs;            jobnext_date  next_sec          failures broken ---------- ----------- ---------------- ----------------          262012/9/14 111:12:08                 0 n          252012/9/14 111:07:18                 0 n

十、總結
關於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)。