oracle通過job定時執行任務

2013年8月27日 星期二 20:49

在項目中，經常會遇到需要定時完成的任務，比如定時更新資料，定義統計資料生成報表等等，其實這些事情都可以使用oracle的job來完成。下面考試大就結合我們實驗室項目實際，簡單介紹一下在oracle資料庫中通過job完成自動建立表的方法。

　　整個過程總共分為兩步。雖然整個過程都非常簡單，但是對于初學oracle的生手還是有很多地方需要注意的。

　　首先介紹一下，建立該job的背景，因為每天更新的直播和點播節目資訊比較多，為了友善處理，需要每天建立一張表來記錄更新的節目資訊，目前資料庫中已經有一張tbl_programme的表，每天建立的表的字段需要同tbl_programme保持一緻，每天新建立的表的名稱格式為tbl_programme_日期（例如：tbl_programme_20090214）規定每天晚上1點鐘生成該天的新表。

　　第一步：建立一個執行建立操作的存儲過程

　　在這一步首先要解決的問題就是構造表名。在oracle中格式化輸出時間可以用to_char函數來處理，例如：

　　得到時間格式字元串後我們就可以将表名的字首和時間連接配接在一起形成完整的表名。這裡需要注意，在oracle中連接配接兩個字元串需要使用‘||’符号，而在sql server中直接使用‘+’号就可以了，因為我以前一直在sql server下程式設計，好久都沒編寫oracle的sql是以費了很大的功夫才發現這個問題。完整的sql就是

　　sql> select ’tbl_programme_’ || to_char(sysdate, ’yyyymmdd’) from dual;

　　’tbl_programme_’||to_char(sysd

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

　　tbl_programme_20090214

　　接下來就是建立表的代碼了，因為新表需要tbl_programme保持一緻，是以直接ctas來建立表那是非常适合的了，代碼如下：

　　create table tablename as select * from tbl_programme

　　如果需要指定一個tablespace則将該sql做适當修改：

　　create table tablename tablespace p2p as select * from tbl_programme

　　是以整個建立存儲過程的sql就是

　　這裡還需要注意一下在oracle裡面如果要對一個變量指派的話有兩種方式：

　　（1）使用：=進行指派

　　（2）使用select ‘xjkxj ’ into 變量名稱 from tabname

　　另外，在存儲過程中定義變量的時候一般放在as/is後begin前面。在存儲過程一般是不能直接使用create table，truncate table這類似的語句的，如果要使用這些語句必須使用excute immediate + 所要執行的sql語句來實作。

　　注意上面用紅色标志的語句：authid current_user

　　這個語句比較重要，如果我們在建立存儲過程的時候不添加這條語句執行該存儲過程将不會成功，原因是預設情況向存儲過程是沒有create table等權限的，即使目前使用者有dba的權限也不行，如果存儲過程中存在建立表的操作，可以有以下兩種方式來解決該問題。

　　（1）顯示的賦予該使用者create table的權限，grant create table to user.

　　（2）在存儲過程中使用authid current_user 辨別使用目前使用者的權限。

　　第二步：建立job

　　建立job就比較簡單了，下面就是建立job的代碼

　　每天晚上1電job啟動一次，執行sp_createtab_tbl_programme存儲過程。

　　這裡需要注意的是，在submit方法的前面一定要先定義job這個變量，另外，submit方法的第二個參數是一個存儲過程的名，記得在後面添加“：”号，在next_date是一個時間類型變量而不是一個字元串，是以需要注意不要把它當成字元串，不需要對該參數加引号。最後一個參數interval是一個字元串類型，記得添加引号。最常見的錯誤如下圖所示：

　　ora-01008： not all variables bound就是沒有定義變量的意思。一定記的在使用submit方法時定義jobid變量。

　　下面是常有的設定interval的方法：

　　2 每天固定時間運作，比如早上8：10分鐘：trunc（sysdate+1） + 8/24

　　² 每天：trunc（sysdate+1）

　　² 每周：trunc（sysdate+7）

　　² 每月：trunc（sysdate+30）

　　² 每個星期日：next_day（trunc（sysdate），’sunday’）

　　² 每天6點：trunc（sysdate+1）+6/24

　　² 半個小時：sysdate+30/1440

　　需要用到的完整sql如下：

　　

第三步：異常情況處理

job不能運作情況處理

　　1.先來了解一下job的參數說明：與job相關的參數一個是job_queue_processes，這個是運作job時候所起的程序數，當然系統裡面job大于這個數值後，就會有排隊等候的，最小值是0，表示不運作job，最大值是36，在os上對應的程序時snpn，9i以後os上管理job的程序叫cjqn.可以使用下面這個sql确定目前有幾個snp/cjq在運作。

　　select * from v$bgprocess，這個paddr不為空的snp/cjq程序就是目前空閑的程序，有的表示正在工作的程序。

　　另外一個是job_queue_interval，範圍在1——3600之間，機關是秒，這個是喚醒job的process，因為每次snp運作完他就休息了，需要定期喚醒他，這個值不能太小，太小會影響資料庫的性能。

　　2.診斷：先确定上面這兩個參數設定是否正确，特别是第一個參數，設定為0了，所有job就不會跑，确認無誤後，我們繼續向下。

　　3.使用下面的sql察看job的的broken，last_date和next_date，last_date是指最近一次job運作成功的結束時間，next_date是根據設定的頻率計算的下次執行時間，根據這個資訊就可以判斷job上次是否正常，還可以判斷下次的時間對不對，sql如下：

　　select * from dba_jobs

　　有時候我們發現他的next_date是4000年1月1日，說明job要不就是在running，要不就是狀态是break（broken=y），如果發現job的broken值為y，找使用者了解一下，确定該job是否可以broken，如果不能broken，那就把broken值修改成n，修改再使用上面的sql察看就發現他的last_date已經變了，job即可正常運作，修改broken狀态的sql如下：

　　declare

　　begin

　　dbms_job.broken（<job_id>，false）；

　　end；

　　4.使用下面的sql查詢是否job還在running

　　select * from dba_jobs_running

　　如果發現job已經run了很久了還沒有結束，就要查原因了。一般的job running時會鎖定相關的相關的資源，可以檢視一下v$access和v$locked_object這兩個view，如果發現其他程序鎖定了與job相關的object，包括pkg/function/procedure/table等資源，那麼就要把其他程序删除，有必要的話，把job的程序也删除，再重新跑看看結果。

　　5.如果上面都正常，但是job還不run，怎麼辦？那我們要考慮把job程序重新開機一次，防止是snp程序死了造成job不跑，指令如下：

　　alter system set job_queue_processes=0 ——關閉job程序，等待5——10秒鐘

　　alter system set job_quene_processes=5 ——恢複原來的值