702
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函數來處理，例如：
SQL> select to_char(sysdate, ’yyyy/mm/dd hh24:mi:ss’) from dual; TO_CHAR(SYSDATE,’YYYY/MM/DDHH2 ------------------------------ 2009/02/14 17:22:41 --以上SQL格式化輸出了時間，要得到我們所需要的格式直接修改一下SQL即可 SQL> select to_char(sysdate, ’yyyymmdd’) from dual; TO_CHAR(SYSDATE,’YYYYMMDD’) --------------------------- 20090214得到時間格式字符串後我們就可以將表名的前綴和時間連接在一起形成完整的表名。這裏需要注意，在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就是
create or replace procedure sp_createtab_tbl_programme Authid Current_User as tabname varchar(200); begin select ’TBL_PROGRAMME_’ || to_char(sysdate, ’yyyymmdd’) into tabname fromdual; --create table tabname as select * from tbl_programme where 1 != 1; execute immediate ’create table ’ || tabname ||’ tablespace p2p as select* from tbl_programme where 1 != 1’; commit; end; /這裏還需要注意一下在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存儲過程。
VARIABLE testjobid number; begin sys.dbms_job.submit(:testjobid,’sp_createtab_tbl_programme;’,trunc(sysdate+1)+1/24,’trunc(sysdate+1)+1/24’); commit; end; /這裏需要注意的是，在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如下：
----------------------------------------------------- -- Export file for user P2P -- -- Created by Administrator on 2009-2-14, 15:45:18 -- ----------------------------------------------------- spool gjgdp2p(v1.3).log promptprompt Creating procedure SP_CREATETAB_TBL_PROGRAMME prompt ============================================= prompt create or replace procedure sp_createtab_tbl_programme Authid Current_User as tabname varchar(200); begin select ’TBL_PROGRAMME_’ || to_char(sysdate, ’yyyymmdd’) into tabname fromdual; --create table tabname as select * from tbl_programme where 1 != 1; execute immediate ’create table ’ || tabname ||’ tablespace p2p as select *from tbl_programme where 1 != 1’; commit; end; / VARIABLE testjobid number; begin sys.dbms_job.submit(:testjobid,’sp_createtab_tbl_programme;’,trunc(sysdate+1)+1/24,’trunc(sysdate+1)+1/24’); commit; end; / spool off
第三步：異常情況處理
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 ——恢複原來的值
最後更新：2017-04-03 16:49:02