Oracle定時器（Job）各時間段寫法匯總

原作者:部落格園一條輝 原文:連結

對於DBA來說，資料庫Job再熟悉不過了，因為經常要資料庫定時的自動執行一些指令碼，或做Database Backup，或做資料的提煉，或做資料庫的效能最佳化，包括重建索引等等的工作。但是，Oracle定時器Job時間的處理上，千變萬化，今天我把比較常用寫法匯總如下： 

在總結之前，先把Job的參數一一說明一下： 

job參數是由Submit()過程返回的binary_ineger。這個值用來唯一標識一個工作； 

what參數是將被執行的PL/SQL代碼塊； 

next_date參數指識何時將運行這個工作。寫Job的時候可以不指定該值； 

interval參數何時這個工作將被重執行。 

其中Interval這個值是決定Job何時，被重新執行的關鍵。 

例如：有預存程序 p_dosomethings，需要被不同的時間間隔執行。 

1declare   
2  jobno number;   
3begin   
4  dbms_job.submit( 
5    jobno, 
6    'p_dosomething;',  --what 
7    to_date('20090101020000','yyyy-mm-dd hh24:mi:ss'),--next_date,可以不填 
8    'Interval時間字串'--interval,關鍵設定 
9  );   
10  commit;   
11end; 
12 

1、 每分鐘執行 

Interval => TRUNC(sysdate,’mi’) + 1 / (24*60) 

2、 每天定時執行 

例如：每天的淩晨2點執行 

Interval => TRUNC(sysdate) + 1 +2 / (24) 

3、 每周定時執行 

例如：每周一淩晨2點執行 

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天 

4、 每月定時執行 

例如：每月1日淩晨2點執行 

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24 

5、 每季度定時執行 

例如每季度的第一天淩晨2點執行 

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 

6、 每半年定時執行 

例如：每年7月1日和1月1日淩晨2點 

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 

7、 每年定時執行 

例如：每年1月1日淩晨2點執行 

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24 

當然上面羅列不可能照顧到方方面面，但是稍微變化一下，就能衍生出無數的例子，聰明的你一定會的吧，我這裡就不多羅嗦了。 

轉摘請註明:部落格園一條輝的部落格(liunx.cnblogs.com) 

補充： 
描述                    INTERVAL參數值 
每天午夜12點            'TRUNC(SYSDATE + 1)' 
每天早上8點30分         'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)' 
每星期二中午12點         'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24' 
每個月第一天的午夜12點    'TRUNC(LAST_DAY(SYSDATE ) + 1)' 
每個季度最後一天的晚上11點 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24' 
每星期六和日早上6點10分    'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）' 

select   *   from   user_jobs 
查詢作業記錄 

1．確保Oracle的工作模式允許啟動任務隊列管理器 
Oracle定時執行“Job Queue”的背景程式是SNP進程，而要啟動SNP進程，首先要確保整個系統的模式是可以啟動SNP進程的，這需要以DBA的身份去執行如下命令： 
svrmgrl>; alter system enable restricted session; 
或sql>; alter system disenable restricted session; 
利用如上命令更改系統的會話方式為disenable restricted，為SNP的啟動創造條件。 

2．確保Oracle的系統已經配置了任務隊列管理器的啟動參數 
SNP的啟動參數位於Oracle的初始設定檔案中，該檔案放在＄ORACLE_HOME/dbs路徑下，如果Oracle的SID是myora8的話，則初始設定檔案就是initmyora8.ora，在檔案中對SNP啟動參數的描述部分如下： 
job_queue_process=n 
job_queue_interval=N 
第一行定義SNP進程的啟動個數為n。系統預設值為0，正常定義範圍為0～36，根據任務的多少，可以配置不同的數值。 
第二行定義系統每隔N秒喚醒該進程一次。系統預設值為60秒，正常範圍為1～3600秒。事實上，該進程執行完當前任務後，就進入睡眠狀態，睡眠一段時間後，由系統的總控負責將其喚醒。 
如果該檔案中沒有上面兩行，請按照如上配置添加。配置完成後，需要重新啟動資料庫，使其生效。注意：如果任務要求執行的間隔很短的話，N的配置也要相應地小一點。 

3．將任務加入到資料庫的任務隊列中 
調用Oracle的dbms_job包中的預存程序，將任務加入到任務隊列中： 
dbms_job.submit( job out binary_integer, 
what　　　　　　　in　　　archar2, 
next_date　　　 　in　　　date， 
interval　　　　　in　　　varchar2, 
no_parse　　　　　in　　　boolean) 
其中： 
●job：輸出變數，是此任務在任務隊列中的編號； 
●what：執行的任務的名稱及其輸入參數； 
●next_date：任務執行的時間； 
●interval：任務執行的時間間隔。 
下面詳細討論一下dbms_job.submit中的參數interval。嚴格地講，interval是指上一次執行結束到下一次開始執行的時間間隔，當interval設定為null時，該job執行結束後，就被從隊列中刪除。假如我們需要該job周期性地執行，則要用‘sysdate＋m’表示。  
將任務加入到任務隊列之前，要確定執行任務的資料庫使用者，若使用者是scott, 則需要確保該使用者擁有執行包dbms_job的許可權；若沒有，需要以DBA的身份將權利授予scott使用者： 
svrmgrl>; grant execute on dbms_job to scott; 

4．將要執行的任務寫成預存程序或其他的資料庫可執行檔pl/sql程式段 
例如，我們已經建立了一個預存程序，其名稱為my_job，在sql/plus中以scott使用者身份登入，執行如下命令： 
sql>; variable n number; 
sql>; begin 
dbms_job.submit(:n‘my_job;’,sysdate, 
‘sysdate＋1/360’); 
commit; 
end; 
/ 
系統提示執行成功。 
Sql>; print :n; 
系統列印此任務的編號，例如結果為300。 
如上，我們建立了一個每隔4分鐘執行一次的任務號為300的任務。可以通過Oracle提供的資料字典user_jobs察看該任務的執行情況： 
sql>; select job,next_date,next_sec,failures,broken from user_jobs; 
執行結果如下： 
job next_date next_sec failures broken 
300 2000/10/10 11:45:15 0 N 
這表示任務號為300的任務，下一次將在2000/10/10 11:45:15執行，此任務的執行失敗記錄為0次。注意：當執行job出現錯誤時，Oracle將其記錄在日誌裡，失敗次數每次自動加1。當執行失敗次數達到16時，Oracle就將該job標誌為broken。此後，Oracle不再繼續執行它，直到使用者調用過程dbms_job.broken，重新設定為not broken，或強制調用dbms_job.run來重新執行它。 
除了以上我們討論的submit預存程序之外，Oracle還提供了其他許多預存程序來操作任務。例如：dbms_job.change 、 dbms_job.what、dbms_job.interval可以用來修改提交的任務。要想刪除該任務，只需運行dbms_job.remove(n)即可，其中n為任務號。 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/ylqmf/archive/2010/01/13/5185595.aspx