oracle定時任務(dbms_job)的時間設定參考

標籤：variable   ora   from   系統時間   next   not   to_date   已耗用時間   建立   

最後那一項可以參考如下：
每天午夜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)‘

其中li_jobno是它的ID,可以通過這個ID停掉這個任務，最後想說的是不要執行多次，你可以在裡面管理起來，發現已經運行了就不SUBMIT

select TRUNC(SYSDATE,‘MONTH‘),last_day(sysdate) from dual

--每個月的第一天和最後一天

每天運行一次 ‘SYSDATE + 1‘
每小時運行一次 ‘SYSDATE + 1/24‘
每10分鐘運行一次 ‘SYSDATE + 10/（60*24）‘
每30秒運行一次 ‘SYSDATE + 30/(60*24*60)‘
每隔一星期運行一次 ‘SYSDATE + 7‘
不再運行該任務並刪除它 NULL
每年1月1號零時   trunc(last_day(to_date(extract(year fromsysdate)||‘12‘||‘01‘,‘yyyy-mm-dd‘))+1

 

-------------------

   Job的參數：

   一：時間間隔執行（每分鐘，每天，每周，:每月，每季度，每半年，每年）

  interval是指上一次執行結束到下一次開始執行的時間間隔，當interval設定為null時，該job執行結束後，

 就被從隊列中刪除。假如我們需要該job周期性地執行，則要用‘sysdate＋m’表示。

   1:每分鐘執行

    Interval=> TRUNC(sysdate,‘mi‘) + 1/(24*60)   或 Interval=> sysdate+1/1440

   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

二：

在一個特定的時間間隔後，重複運行該任務。
在特定的日期和時間運行任務。
任務成功完成後，下一次執行應該在一個特定的時間間隔之後。
第一種調度任務需求的日期演算法比較簡單，即‘SYSDATE+n‘,這裡n是一個以天為單位的時間間隔。表1給出了一些這種時間間隔設定的例子。

表1 一些簡單的interval參數設定例子

描述 Interval參數值
每天運行一次 ‘SYSDATE + 1‘
每小時運行一次 ‘SYSDATE + 1/24‘
每10分鐘運行一次 ‘SYSDATE + 10/（60*24）‘
每30秒運行一次 ‘SYSDATE + 30/(60*24*60)‘
每隔一星期運行一次 ‘SYSDATE + 7‘
不再運行該任務並刪除它 NULL

表1所示的任務間隔運算式不能保證任務的下一次已耗用時間在一個特定的日期或者時間，僅僅能夠指定一個任務兩次運行之間的時間間隔。例如，如果一個任務第一次運行是在淩晨12點，interval指定為‘SYSDATE+1‘,則該任務將被計劃在第二天的淩晨12點執行。但是，如果某使用者在下午4點手工（DBMS_JOB.RUN）執行了該任務，那麼該任務將被重新定時到第二天的下午4點。還有一個可能的原因是如果資料庫關閉或者說任務隊列非常的忙以至於任務不能在計劃的那個時間點準時執行。在這種情況下，任務將試圖儘快運行，也就是說只要資料庫一開啟或者是任務隊列不忙就開始執行，但是這時，已耗用時間已經從原來的提交時間漂移到了後來真正的已耗用時間。這種下一次已耗用時間的不斷“漂移”是採用簡單時間間隔運算式的典型特徵。

第二種調度任務需求相對於第一種就需要更複雜的時間間隔（interval）運算式，表7是一些要求在特定的時間運行任務的interval設定例子。

表 2. 定時到特定日期或時間的任務例子

描述 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）‘

第三種調度任務需求無論通過怎樣設定interval日期運算式也不能滿足要求。這時因為一個任務的下一次已耗用時間在任務開始時才計算，而在此時是不知道任務在何時結束的。遇到這種情況怎麼辦呢？當然辦法肯定是有的，我們可以通過為任務隊列寫過程的辦法來實現。這裡我只是簡單介紹以下，可以在前一個任務隊列執行的過程中，取得任務完成的系統時間，然後加上指定的時間間隔，拿這個時間來控制下一個要執行的任務。這裡有一個前提條件，就是目前啟動並執行任務本身必須要嚴格遵守自己的時間計劃。

將任務加入到任務隊列之前，要確定執行任務的資料庫使用者，若使用者是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,brokenfrom user_jobs;
執行結果如下：
job next_date next_sec failures broken
300 2000/10/10 11:45:15 0 N
這表示任務號為300的任務，下一次將在2000/10/1011:45:15執行，此任務的執行失敗記錄為0次。注意：當執行job出現錯誤時，Oracle將其記錄在日誌裡，失敗次數每次自動加1。當執行失敗次數達到16時，Oracle就將該job標誌為broken。此後，Oracle不再繼續執行它，直到使用者調用過程dbms_job.broken，重新設定為notbroken，或強制調用dbms_job.run來重新執行它。

除了以上我們討論的submit預存程序之外，Oracle還提供了其他許多預存程序來操作任務。例如：dbms_job.change、dbms_job.what、dbms_job.interval可以用來修改提交的任務。要想刪除該任務，只需運行dbms_job.remove(n)即可，其中n為任務號。

 總結：

1、 每分鐘執行

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

2、 每天定時執行

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

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

3、 每周定時執行

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

Interval =>TRUNC(next_day(sysdate,‘星期一‘))+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‘),6)+2/24

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

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

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

oracle定時任務(dbms_job)的時間設定參考