Oracle定時任務

一、DBMS_JOBS

DBMS_JOB包由$ORACLE_HOME/rdbms/admin/dbmsjob.sql和prvtjob.plb兩個指令碼檔案建立，其中這兩個檔案被catproc.sql在建立資料庫後被立即呼叫執行。指令碼為DBMS_JOB包建立了一個公共同義詞，並給DBMS_JOB包賦予了公共的可執行許可權，所有Oracle使用者都可使用DBMS_JOB包。

常用資料字典有DBA_JOBS、USER_JOBS、DBA_JOBS_RUNNING,這些資料字典由catjobq.sql指令碼建立，catjobq.sql同樣也在建立資料庫後被catproc.sql呼叫執行。

若要Job可以被正常執行，需要將資料庫中的引數job_queue_processes設定為大於0的一個數字，即job的佇列過程數，隨著Oracle啟動而啟動SNP（任務佇列後臺過程），最多可啟動36個SNP。
1.DBMS_JOB包含的過程

名稱	描述	型別
DBMS_JOB.ISUBMIT	提交一個新任務，使用者指定一個任務號	過程
DBMS_JOB.SUBMIT	提交一個新任務，系統指定一個任務號	過程
DBMS_JOB.REMOVE	從佇列中刪除一個已存在的任務	過程
DBMS_JOB.CHANGE	更改使用者設定的任務引數	過程
DBMS_JOB.WHAT	更改PL/SQL任務定義	過程
DBMS_JOB.NETX_DATE	更改任務下一次執行時間	過程
DBMS_JOB.INSTNACE		過程
DBMS_JOB.INTERVAL	更改任務執行的時間間隔	過程
DBMS_JOB.BROKEN	將任務掛起，不讓其重複執行	過程
DBMS_JOB.RUN	當前會話中立即執行任務	過程
DBMS_JOB.USER_EXPORT	建立文字字串，用於重新建立一個任務	過程
DBMS_JOB.IS_JOBQ		函式
DBMS_JOB.BACKGROUND_PROCESS		函式

2.DBMS_JOB包引數

名稱	型別	註釋
Job	Binary_integer	任務的唯一識別碼
What	Varchar2	作為任務執行的PL/SQL程式碼
Next_date	Varchar2	任務下一次執行的時間
Interval	Varchar2	日期表示式，用來計算下一次任務執行的時間

Job說明：
job引數是一個整數，用來唯一標示一個任務。該引數可以由使用者指定也可以系統自動生成，其取決於提交任務時選用的任務提交儲存過程。如果使用dbms_job.submit過程透過獲得序列sys.jobseq的下一個值自動賦值任務號，如果使用dbms_jobisubmit過程則呼叫者給任務指定一個識別號。任務號只有在刪除或者重新提交任務時可以變更，其他情況不會變更。

What說明：

what引數是一個可以轉化為合服PL/SQL呼叫的字串,該呼叫將被任務佇列自動執行。如果引數中使用了文字字串，需要將字串用單引號括起來，而PL/SQL必須用分號分隔，例如：

what =>’my_procedure(parameter1);’

what =>’my_procedure(parameter1);end;’ ##安全寫法

需要注意的是透過當前會話設定的what引數會被記錄下來當做任務執行環境的一部分，而且what引數限制在2000位元組以內。

Next_date說明

Next_date引數用來排程任務佇列中該任務下一次執行的時間，這個引數對於dbms_job.submit和dbms_job.broken兩個儲存過程預設為系統當前時間，即任務立即執行。

      當一個任務的next_date引數賦值為null時，則該任務下一次執行的時間將被指定為4000年1月1日，即任務將永遠不再被執行，其用以保留任務而不讓其執行。也可以將這個引數值設定為過去的某個時間點。
    Interval引數說明
        Interval引數是一個表示Oracle合法日期表示式的字串。這個日期字串的值在每次任務被執行時算出，其結果值要麼是未來的某個時間要麼為null。當interval的值為null時，則該任務僅被執行一次。

3.DBMS_JOB對應的ora初始化引數
    Job_queue_interval
        任務佇列規程定期喚醒並檢查佇列目錄表是否有任務需要被執行，其決定SNP過程兩次檢查目錄表休眠多少時間，單位是秒。預設設定為60S，超出這個時間範圍的JOB不會被執行。
    Job_queue_keep_connections
        引數值為true和false，表示SNP兩個任務執行期間（休眠間隔），當true時任然保持和Oracle的連線，當false時SNP斷開和資料庫連線，當喚醒時又重新連線並檢查任務佇列。主要考慮佇列的有效性和資料庫的關閉方法，一般執行頻繁的任務可以設定為true，執行不頻繁的設定為false即可。
    Job_queue_processes
        任務佇列數。

4. DBMS_JOB執行過程

①以任務所有者使用者開啟一個新資料庫會話；

②當任務第一次提交或最後一次修改完成時，更改會話的NLS和目前就緒的任務相匹配；

③透過interval日期表示式和系統時間，計算下一次任務執行時間；

④執行定義的PL/SQL儲存過程；

⑤如果執行成功，則任務下一次執行日期（Next_date）被更新，否則失敗計數加1，累積達16次則job終止執行，或者超出執行範圍job同樣終止執行；

⑥經過job_queue_interval秒後，又到另一個任務執行，重複上述工作。

注意：

當任務執行失敗時，SNP過程在1分鐘後將再次試圖執行該任務。如果這次執行又失敗了，下一次嘗試將在2分鐘後進行，再下一次在4分鐘以後。任務佇列每次加倍重試間隔直到它超過了正常的執行間隔。在連續16次失敗後，任務就被標記為中斷的（broken），如果沒有使用者干預，任務佇列將不再重複執行。

5.DBMS_JOB任務佇列檢視

任務佇列資料字典：

檢視名	描述
DBA_JOBS	本資料庫中定義到任務佇列中的任務
DBA_JOBS_RUNNING	目前正在執行的任務
USER_JOBS	當前使用者擁有的任務

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)	任務執行的其他一些會話引數

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日期的小時，分鐘和秒

6.DBMS_JOB時間間隔設定

任何重複執行的任務時間間隔取決於interval引數設定的日期表示式，一個定時任務一般設定要求有三種，分別是：

（1）在一個特定時間間隔後，重複執行該任務；可用’sysdate + N' ,N以天為單位。

（2）在特定時間和日期執行任務；

（3）任務完成後，下一次執行任務在一個特定的時間間隔後。

時間間隔interval設定示例1：（無法指定特定日期和時間）

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

注意：

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

時間間隔interval設定示例2：（定時到特定時間和日期）

描述	INTERVAL引數值
每天午夜12點	'TRUNC(SYSDATE + 1)'
每天早上8點30分	'TRUNC(SYSDATE + 1) +（860+30）/(2460)'
每星期二中午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）'

7.DBMS_JOB建立示例

declare

jid number;

begin

dbms_job.submit(job => jid,

what => 'begin insert into old_job_test values(sysdate, ''abc''); commit; end;',

interval => 'trunc(sysdate,''mi'')+3/24/60');

commit;

dbms_output.put_line(jid);

end;

二、DBMS_Scheduler

1. DBMS_Scheduler建立示例

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'INSERT_TEST_TBL',

job_type => 'STORED_PROCEDURE',

job_action => ' P_ INSERT INTOTEST ',

start_date => sysdate,

repeat_interval => 'FREQ=DAILY;INTERVAL=1');

END;

/

Job_name指定任務名稱，名稱必須唯一，Job_name可以指定到特定使用者下，例如scott.job_name。

Job_Type任務執行的操作型別，可以是以下型別

⑴plsql_block表示任務執行的是一個PL/SQL匿名塊

⑵、stored_procedure表示指定的Oracle過程（含PL/SQL Procedure和Java Procedure） executable表示執行的是一個外部程式，比如說系統命令

⑶、chain表示任務執行的是一個chain

Job_Action任務執行操作，應與Job_type指定的引數相匹配

Start_Date指定任務初次執行的時間，可以為空，空值表示立即執行，空值等同於sysdate。

REPEAT_INTERVAL指定任務執行頻率，隔多長時間再次執行，可以為空值，空值表示只執行一次，其中包含的重要引數有FREQ和INTERVAL，FREQ可以為YEARLY,MONTHLY,WEEKLY,DAILY,HOURLY,MINUTELY,SECONDLY,表示年、月、日、時、分、秒，INTERVAL表示時間間隔頻率，取值範圍為1-99。示例表示每天執行一次，如果INTERVAL是7，則表示每隔7天執行一次等同於weekly , 1。

ENABLED指定任務是否啟動，預設是False表示該任務不會被執行，除非手動呼叫，或使用者手動將該引數設定為TRUE。

RESTARTABLE指定jobs執行出錯後，是否能夠適時重啟建立任務時如未明確指定，本引數預設情況下設定為FALSE，如果設定為TRUE，就表示當任務執行時出錯，下次執行時間點到達時仍會啟動，並且如果執行仍然出錯，會繼續重新執行，不過如果連線出錯達到6次，該job就會停止。

MAX_FAILURES指定jobs最大連續出錯次數該引數值可指定的範圍從1-1000000，預設情況下該引數設定為NULL，表示無限制。達到指定出錯次數後，該job會被自動disable。

2. DBMS_Scheduler相關檢視

DBA_SCHEDULER_JOBS

ALL_SCHEDULER_JOBS

USER_SCHEDULER_JOBS

DBA_SCHEDULER_JOB_LOG

3. DBMS_Scheduler管理

（1）、啟用Jobs

建立Jobs的時候沒有顯示指定ENABLED引數，即時指定了START_DATE引數，預設情況下JOB也不會被呼叫，因此可以透過修改JOB的啟動狀態，具體如下：

exec dbms_scheduler.enable(“JOB_NAME”) ;

（2）、禁用Jobs

exec dbms_scheduler.disable(“JOB_NAME”) ;

（3）、修改Jobs

exec dbms_scheduler.set_attribute("INSERT_TEST_TBL","JOB_ACTION","P_ INSERT INTOTEST");

（4）、執行Jobs

exec dbms_scheduler.run_job("Job_name ");

（5）、停止Jobs

exec dbms_scheduler.stop_job("Job_name");

（6）、刪除Jobs

exec dbms_scheduler.drop_job("Job_name");

（7）建立Jobs

begin

sys.dbms_scheduler.create_job(job_name =>'SCOTT.TEST_JOB',

job_type =>'STORED_PROCEDURE',

job_action =>'t1_pro',

start_date =>to_date('27-11-2015 09:06:47','dd-mm-yyyy hh24:mi:ss'),

repeat_interval =>'Freq=daily;Interval=1;ByHour=10;ByMinute=30;BySecond=30',

end_date =>to_date(null),

job_class =>'DEFAULT_JOB_CLASS',

enabled =>true,

auto_drop =>true,

comments =>'');

end;

/

4. DBMS_Scheduler Repeat_interval設定詳解

repeat_interval => =>'Freq=daily;Interval=1;ByMonth=Feb;ByWeekNo=17;ByYearDay=7;ByMonthDay=9;ByDay=Wed;ByHour=10;ByMinute=52;BySecond=00'

引數名	引數值
FREQ	YEARLY\|MONTHLY\|WEEKLY\|DAILY\|HOURLY\|MINUTLY\|SECONDLY
INTERVAL	[1-99]
BYMONTH	[1-12] OR JAN\|FEB\|MAR\|APR\|MAY\|JUN\|JUL\|AUG\|DEP\|OCT\|NOV\|DEC
BYWEEKNO	[1-53] OR [-53,-1]
BYYEARDAY	[1-366] OR [-366,-1]
BYMONTHDAY	[1-31] OR [-31,-1]
BYDAY	MON\|TUE\|WED\|THU\|FRI\|SAT\|SUN
BYHOUR	[0-23]
BYMINUTE	[0-59]
BYSECOND	[0-59]

每隔10分鐘執行一次：

REPEAT_INTERVAL => 'FREQ=HOURLY; BYMINUTE=0,10,20,30,40,50; BYSECOND=0'或

REPEAT_INTERVAL => 'FREQ=MINUTELY; BYMINUTE=0,10,20,30,40,50; BYSECOND=0'

每週五執行：

REPEAT_INTERVAL => ‘FREQ=DAILY; BYDAY=FRI’; 或

REPEAT_INTERVAL => ‘FREQ=WEEKLY; BYDAY=FRI’;或

REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDAY=FRI’;

當月最後一天執行：

REPEAT_INTERVAL => ‘FREQ=MONTHLY; BYMONTHDAY=-1’;

每天下午4,5,6點定時執行：

REPEAT_INTERVAL => ‘FREQ=DAILY; BYHOUR=16,17,18’;

只執行一次：

repeat_interval => null

BEGIN

dbms_scheduler.create_schedule(repeat_interval => null,

start_date => systimestamp,

schedule_name => 'scheduler_0306_2');

dbms_scheduler.create_job(job_name => 'job_0306_2',

program_name => 'program_0306',

schedule_name => 'scheduler_0306_2',

auto_drop => FALSE,

enabled => TRUE);

END;

5. DBMS_Scheduler注意點

（1）設定執行時間，注意系統時間和時區的設定

Linux

更改/etc/timezone裡面的內容為:Asia/Shanghai
#mv /etc/localtime /etc/localtime-2015
# cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

Oracle

select dbtimezone from dual;

select sessiontimezone from dual;

alter database set time_zone='+08:00';

Oracle 定時任務詳解