oracle之定時任務[轉]

oracle定時任務[轉]

2008-11-12 — Unmi

DBMS_JOB系統包是Oracle“任務隊列”子系統的API程式設計接口。DBMS_JOB包對于任務隊列提供了下面這些功能：送出并且執行一個任務、改變任務的執行參數以及删除或者臨時挂起任務等。

DBMS_JOB包是由ORACLE_HOME目錄下的rdbms/admin子目錄下的DBMSJOB.SQL和PRVTJOB.PLB 這兩個腳本檔案建立的。這兩個檔案被CATPROC.SQL腳本檔案調用，而CATPROC.SQL這個檔案一般是在資料庫建立後立即執行的。腳本為DBMS_JOB包建立了一個公共同義詞，并給該包授予了公共的可執行權限，是以所有的Oracle使用者均可以使用這個包。

下面幾個資料字典視圖是關于任務隊列資訊的，主要有DBA_JOBS, USER_JOBS和DBA_JOBS_RUNNING。這些字典視圖是由名為CATJOBQ.SQL的腳本檔案建立的。該腳本檔案和建立DBMS_JOB包的腳本檔案一樣在ORACLE_HOME目錄的rdbms/admin子目錄中，同樣也是由腳本檔案CATPROC.SQL調用。

最後，要使任務隊列能正常運作，還必須啟動它自己專有的背景過程。啟動背景過程是通過在初始化檔案init*.ora(執行個體不同，初始化檔案名也略有不同)中設定初始化參數來進行的。下面就是該參數：

JOB_QUEUE_PROCESSES = n

其中，n可以是0到36之間的任何一個數。除了該參數以外，還有幾個關于任務隊列的初始化參數，本文後面将會對其進行詳細讨論。

DBMS_JOB包中包含有許多過程，見表1所示。

表1 DBMS_JOB包

名稱	類型	描述
DBMS_JOB.ISUBMIT	過程	送出一個新任務，使用者指定一個任務号
DBMS_JOB.SUBMIT	過程	送出一個新任務，系統指定一個任務号
DBMS_JOB.REMOVE	過程	從隊列中删除一個已經存在的任務
DBMS_JOB.CHANGE	過程	更改使用者設定的任務參數
DBMS_JOB.WHAT	過程	更改PL/SQL任務定義
DBMS_JOB.NEXT_DATE	過程	更改任務下一次運作時間
DBMS_JOB.INTERVAL	過程	更改任務運作的時間間隔
DBMS_JOB.BROKEN	過程	将任務挂起，不讓其重複運作
DBMS_JOB.RUN	過程	在目前會話中立即執行任務
DBMS_JOB.USER_EXPORT	過程	建立文字字元串，用于重新建立一個任務

三、DBMS_JOB包參數

DBMS_JOB包中所有的過程都有一組相同的公共參數，用于定義任務，任務的運作時間以及任務定時運作的時間間隔。這些公共任務定義參數見表2所示。

表2 DBMS_JOB過程的公共參數

名稱	類型	注釋
Job	BINARY_INTEGER	任務的唯一識别号
What	VARCHAR2	作為任務執行的PL/SQL代碼
Next_date	VARCHAR2	任務下一次運作的時間
Interval	VARCHAR2	日期表達式，用來計算下一次任務運作的時間

下面我們來詳細讨論這些參數的意義及用法。

1、job

參數job是一個整數，用來唯一地标示一個任務。該參數既可由使用者指定也可由系統自動賦予，這完全取決于送出任務時選用了那一個任務送出過程。DBMS_JOB.SUBMIT過程通過獲得序列SYS.JOBSEQ的下一個值來自動賦予一個任務号。該任務号是作為一個OUT參數傳回的，是以調用者随後可以識别出送出的任務。而DBMS_JOB.ISUBMIT過程則由調用者給任務指定一個識别号，這時候，任務号的唯一性就完全取決于調用者了。

除了删除或者重新送出任務，一般來說任務号是不能改變的。即使當資料庫被導出或者被導入這樣極端的情況，任務号也将被保留下來。是以在執行含有任務的資料的導入/導出操作時很可能會發生任務号沖突的現象。

2、what

what參數是一個可以轉化為合法PL/SQL調用的字元串，該調用将被任務隊列自動執行。在what參數中，如果使用文字字元串，則該字元串必須用單引号括起來。 what參數也可以使用包含我們所需要字元串值的VARCHAR2變量。實際的PL/SQL調用必須用分号隔開。在PL/SQL調用中如果要嵌入文字字元串，則必須使用兩個單引号。

what參數的長度在Oracle7.3中限制在2000個位元組以内，在Oracle 8.0以後，擴大到了4000個位元組，這對于一般的應用已完全足夠。該參數的值一般情況下都是對一個PL/SQL存儲過程的調用。在實際應用中，盡管可以使用大匿名Pl/SQL塊，但建議大家最好不要這樣使用。還有一個實際經驗就是最好将存儲過程調用封裝在一個匿名塊中，這樣可以避免一些比較莫名錯誤的産生。我來舉一個例子，一般情況下，what參數可以這樣引用：

what =>’my_procedure(parameter1);’

但是比較安全的引用，應該這樣寫：

what =>’begin my_procedure(parameter1); end;’

任何時候，我們隻要通過更改what參數就可以達到更改任務定義的目的。但是有一點需要注意，通過改變what參數來改變任務定義時，使用者目前的會話設定也被記錄下來并成為任務運作環境的一部分。如果目前會話設定和最初送出任務時的會話設定不同，就有可能改變任務的運作行為。意識到這個潛在的副作用是非常重要的，無論何時隻要應用到任何DBMS_JOB過程中的what參數時就一定要確定會話設定的正确。

3、next_date

Next_date參數是用來排程任務隊列中該任務下一次運作的時間。這個參數對于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN這兩個過程确省為系統目前時間，也就是說任務将立即運作。

當将一個任務的next_date參數指派為null時，則該任務下一次運作的時間将被指定為4000年1月1日，也就是說該任務将永遠不再運作。在大多數情況下，這可能是我們不願意看到的情形。但是，換一個角度來考慮，如果想在任務隊列中保留該任務而又不想讓其運作，将next_date設定為null卻是一個非常簡單的辦法。

Next_date也可以設定為過去的一個時間。這裡要注意，系統任務的執行順序是根據它們下一次的執行時間來确定的，于是将next_date參數設定回去就可以達到将該任務排在任務隊列前面的目的。這在任務隊列程序不能跟上将要執行的任務并且一個特定的任務需要盡快執行時是非常有用的。

4、Interval

Internal參數是一個表示Oracle合法日期表達式的字元串。這個日期字元串的值在每次任務被執行時算出，算出的日期表達式有兩種可能，要麼是未來的一個時間要麼就是null。這裡要強調一點：很多開發者都沒有意識到next_date是在一個任務開始時算出的，而不是在任務成功完成時算出的。

當任務成功完成時，系統通過更新任務隊列目錄表将前面算出的next_date值置為下一次任務要運作的時間。當由interval表達式算出next_date是null時，任務自動從任務隊列中移出，不會再繼續執行。是以，如果傳遞一個null值給interval參數，則該任務僅僅執行一次。

通過給interval參數賦各種不同的值，可以設計出複雜運作時間計劃的任務。本文後面的“任務間隔和日期算法”将對interval表達式進行詳細讨論，并給出一個實際有用interval表達式的例子。

四、任務隊列架構和運作環境

任務隊列在Oracle系統中其實是一個子系統，它具有自己特定的背景過程和目錄表。該子系統設計的目的是為了能不在使用者幹預下自動運作PL/SQL過程。

1、任務隊列背景過程

任務隊列（SNP）背景過程随着Oracle執行個體的啟動而同時啟動。在文章前面已經談到初始化檔案init.ora中的參數JOB_QUEUE_PROCESSES，用來設定有幾個隊列過程。這裡設定了幾個過程，系統中就會有幾個SNP過程被啟動。JOB_QUEUE_PROCESSES這個參數，可以是0到36中的任何一個數，也就是說對于每個Oracle執行個體最多可以有36個SNP過程，也可以不支援隊列過程（＝0）。在大多數作業系統中，SNP三個字母常作為過程名的一部分出現。如，在unix系統中，如果該Oracle執行個體名為ora8，有三個任務隊列過程，則這三個任務隊列過程名稱為：

ora_ora8_snp0 ora_ora8_snp1 ora_ora8_snp2

SNP背景過程和其他的Oracle背景過程的一個重要差別就是殺掉一個SNP過程不會影響到Oracle執行個體。當一個任務隊列過程失控或者消耗太多的資源時，就可以将其殺掉，當然這種情況不是經常遇到的。當一個SNP過程被殺掉或者失敗時，Oracle就自動啟動一個新的SNP過程來代替它。

2、有關任務隊列的初始化參數

初始化檔案init.ora中的幾個參數控制着任務隊列背景的運作，下面我們将對其進行詳細讨論。

（1）、JOB_QUEUE_INTERVAL

任務隊列過程定期喚醒并檢查任務隊列目錄表是否有任務需要執行。參數JOB_QUEUE_INTERVAL決定SNP過程兩次檢查目錄表之間“休眠”多長時間（機關為秒）。間隔設的太小會造成由于SNP過程不斷檢查目錄表而導緻不必要的系統吞吐量。相反如果間隔設得太大，SNP過程在特定的時間沒有被喚醒，那個時間的任務就不會能被運作。最佳的時間間隔設定要綜合考慮系統環境中不同的任務，60秒的确省設定可以滿足大多數的應用。

（2）、JOB_QUEUE_KEEP_CONNECTIONS

除了前面介紹的JOB_QUEUE_PROCESS和JOB_QUEUE_INTERVAL兩個參數以外，影響SNP背景過程行為的第三個參數是JOB_QUEUE_KEEP_CONNECTIONS。當該參數為TRUE時，SNP過程在兩個任務的運作期間（也就是休眠期間），仍然和Oracle保持開放的連接配接。相反，如果為FALSE時，SNP過程将和資料庫斷開連接配接，當喚醒時刻到來時又重新連接配接并檢查任務隊列。

選擇這兩種方法中的那一種，主要是考慮任務隊列的有效性和資料庫關閉方法。長期保持連接配接的效率比較高，但任務隊列會受到正常關閉資料庫的影響。這是因為任務隊列過程對于伺服器管理器看來和一個普通使用者的過程沒有什麼不同，而正常的關閉資料庫需要讓所有的使用者都斷開連接配接。而斷開連接配接和重新連接配接又給資料庫增加了負荷，但是可定期地使資料庫沒有可連接配接SNP過程，也就可以使資料庫正常關閉。對于有很多任務或者是任務重複執行的時間間隔較短（一個小時或者更少）的環境，一般将JOB_QUEUE_KEEP_CONNECTIOONS設定為TRUE，并修改關閉資料庫的腳本為立即關閉。對于嚴格要求采用正常方式關閉的資料庫或者是任務較少，重複間隔較長的環境，一般将該參數設定為FALSE。最好，要提醒一句，SNP過程僅在沒有任何任務運作時才斷開，這種情況下，那些需要比較長時間運作的任務SNP将在它們的生命周期内一緻保持開放的連接配接，這就延遲了正常關閉資料庫的時間。

3、建立運作環境

當SNP過程喚醒時，它首先檢視任務隊列目錄中所有的任務是否目前的時間超過了下一次運作的日期時間。SNP檢測到需要該時間立即執行的任務後，這些任務按照下一次執行日期的順序依次執行。當SNP過程開始執行一個任務時，其過程如下：

1. 以任務所有者的使用者名開始一個新的資料庫會話。
2. 當任務第一次送出或是最後一次被修改時，更改會話NLS設定和目前就緒的任務相比對。
3. 通過interval日期表達式和系統時間，計算下一次執行時間。
4. 執行任務定義的PL/SQL
5. 如果運作成功，任務的下一次執行日期（next_date）被更新，否則，失敗計數加1。
6. 經過JOB_QUEUS_INTERVAL秒後，又到了另一個任務的運作時間，重複上面的過程。

在前兩步中，SNP過程建立了一個模仿使用者運作任務定義的PL/SQL的會話環境。然而，這個模仿的運作環境并不是和使用者實際會話環境完全一樣，需要注意以下兩點：第一，在任務送出時任何可用的非确省角色都将在任務運作環境中不可用。是以，那些想從非确省角色中取得權限的任務不能送出，使用者确省角色的修改可以通過在任務未來運作期間動态修改來完成。第二，任何任務定義本身或者過程執行中需要的資料庫聯接都必須完全滿足遠端的使用者名和密碼。SNP過程不能在沒有顯式指明密碼的情況下初始化一個遠端會話。顯然，SNP過程不能假定将本地使用者的密碼作為遠端運作環境會話設定的一部分。

送出的任務如果運作失敗會怎麼樣呢？當任務運作失敗時，SNP過程在1分鐘後将再次試圖運作該任務。如果這次運作又失敗了，下一次嘗試将在2分鐘後進行，再下一次在4分鐘以後。任務隊列每次加倍重試間隔直到它超過了正常的運作間隔。在連續16次失敗後，任務就被标記為中斷的（broken），如果沒有使用者幹預，任務隊列将不再重複執行。

五、任務隊列字典表和視圖

任務隊列中的任務資訊可以通過表3所示的幾個字典視圖來檢視，這些視圖是由CATJOBQ.sql腳本建立的。表4和5是各個視圖每個字段的含義。

表3. 任務隊列中關于任務的資料字典視圖

視圖名	描述
DBA_JOBS	本資料庫中定義到任務隊列中的任務
DBA_JOBS_RUNNING	目前正在運作的任務
USER_JOBS	目前使用者擁有的任務

表4. DBA_JOBS 和 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	下一次定時運作任務的時間
NEXT_SEC	VARCHAR2(8)	如HH24:MM:SS格式的next_date日期的小時，分鐘和秒
TOTAL_TIME	NUMBER	該任務運作所需要的總時間，機關為秒
BROKEN	VARCHAR2(1)	标志參數，Y标示任務中斷，以後不會運作
INTERVAL	VARCHAR2(200)	用于計算下一運作時間的表達式
FAILURES	NUMBER	任務運作連續沒有成功的次數
WHAT	VARCHAR2(2000)	執行任務的PL/SQL塊
CURRENT_SESSION_LABEL	RAW MLSLABEL	該任務的信任Oracle會話符
CLEARANCE_HI	RAW MLSLABEL	該任務可信任的Oracle最大間隙
CLEARANCE_LO	RAW MLSLABEL	該任務可信任的Oracle最小間隙
NLS_ENV	VARCHAR2(2000)	任務運作的NLS會話設定
MISC_ENV	RAW(32)	任務運作的其他一些會話參數

表 5. 視圖DBA_JOBS_RUNNING的字段含義

列	資料類型	描述
SID	NUMBER	目前正在運作任務的會話ID
JOB	NUMBER	任務的唯一标示符
FAILURES	NUMBER	連續不成功執行的累計次數
LAST_DATE	DATE	最後一次成功執行的日期
LAST_SEC	VARCHAR2(8)	如HH24:MM:SS格式的last_date日期的小時，分鐘和秒
THIS_DATE	DATE	目前正在運作任務的開始日期
THIS_SEC	VARCHAR2(8)	如HH24:MM:SS格式的this_date日期的小時，分鐘和秒

六、任務重複運作間隔和間隔設計算法

任務重複運作的時間間隔取決于interval參數中設定的日期表達式。下面就來詳細談談該如何設定interval參數才能準确滿足我們的任務需求。一般來講，對于一個任務的定時執行，有三種定時要求。

1. 在一個特定的時間間隔後，重複運作該任務。
2. 在特定的日期和時間運作任務。
3. 任務成功完成後，下一次執行應該在一個特定的時間間隔之後。

第一種排程任務需求的日期算法比較簡單，即'SYSDATE+n',這裡n是一個以天為機關的時間間隔。表6給出了一些這種時間間隔設定的例子。

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

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

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

第二種排程任務需求相對于第一種就需要更複雜的時間間隔（interval）表達式，表7是一些要求在特定的時間運作任務的interval設定例子。

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

描述	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日期表達式也不能滿足要求。這時因為一個任務的下一次運作時間在任務開始時才計算，而在此時是不知道任務在何時結束的。遇到這種情況怎麼辦呢？當然辦法肯定是有的，我們可以通過為任務隊列寫過程的辦法來實作。這裡我隻是簡單介紹以下，可以在前一個任務隊列執行的過程中，取得任務完成的系統時間，然後加上指定的時間間隔，拿這個時間來控制下一個要執行的任務。這裡有一個前提條件，就是目前運作的任務本身必須要嚴格遵守自己的時間計劃。

結論

Oracle中的定時任務是在Oracle系統中是一個非常重要的子系統，運用得當，可以極大的提高我們的系統運作和維護能力。而Oracle資料複制的延遲事務隊列管理完全是基于Oracle的隊列任務，對其的深刻了解有助于我們更好地管理資料複制。

原文位址：http://blog.csdn.net/li_guang/archive/2008/06/04/2510846.aspx

可以在控制台殺除相應的 snp 排程 Job 的程序

[email protected] # ps -ef|grep snp

ora805 3745 1 6 10:51:52 ? 9:17 ora_snp1_PROD

ora805 3749 1 4 10:51:52 ? 3:23 ora_snp3_PROD

ora805 3751 1 6 10:51:52 ? 6:52 ora_snp4_PROD

ora805 3747 1 6 10:51:52 ? 8:58 ora_snp2_PROD

ora805 3770 1 4 10:52:56 ? 4:36 ora_snp0_PROD

[email protected] # kill -9 3745 3749

殺了某些 job_queue_process 後 Oracle 又會自動啟動相應資料的 snp 程序。

sqlplus 中可用 select * from dba_jobs_running 顯示正在執行的 Job，記錄數一般就是 job_queue_processes 參數的數量。

show parameter job_queue_processes 顯示程序數

alter system set job_queue_processes=10 [scope=memory|scope=spfile|scope=both] 來設定排程 job 的程序數。

本文連結 http://unmi.cc/oracle-schedule-job, 來自 隔葉黃莺 Unmi Blog