Oracle Scheduler學習筆記分享
Oracle Scheduler學習筆記分享
初次接觸oracle scheduler會覺得這東西並不難，但是涉及到方方面面的概念比較多，看似比較分散的知識點間往往又存在較為緊密的關聯，技術細節的掌握成為了用好oracle scheduler的關鍵。下面是我在oracle scheduler學習過程中的總結和提煉出的一些內容，希望對正在學習oracle scheduler的同學有所幫助。如果對基本概念不是很清楚建議還是先看一下oracle的官方文件。
第一部分：Oracle scheduler體系結構裡的各個物件型別及其使用方法
1. Program
避免在每個job定義時都在job_action引數定義一長pl/sql程式碼或者shell命令，也為了在不同的job間實現對相同程式的複用；
其中的STORED_PROCEDURE、EXTERNAL型別能夠接收由define_program_argument、define_metadata_argument這兩個過程所定義的外部引數型別，PLSQL_BLOCK型別不適用上述兩個過程，但能在其程式碼裡直接使用job_name、job_subname、job_start等變數來引用job執行過程中產生的metadata；
Program建立時預設為Disable狀態，即便在create_job引用到相關program時，這個program仍然可以為Disable狀態，直到最後enable job時才會要求program一定處於Enable狀態；但是有一種情況例外：當建立job_style=lightweight型別的job時，program必須處於Enable狀態，而且對於lightweight job program型別必須是'PLSQL_BLOCK'或'STORED_PROCEDURE'；
Detach=yes屬性的program在結束之前必須使用END_DETACHED_JOB_RUN通知主job，否則job將一直處於running狀態
2. Schedule
執行時刻表，避免在每個job定義時都繁瑣的定義start_Date、end_date、repeat_interval等引數，對於執行時間相同的能夠共用同一個schedule；
Scheduler建立完以後就是enabled，且能夠被任何使用者使用，不需要額外賦權；
建立scheduler的repeat_interval引數必須使用Calendaring Expression方式指定，例如每2小時執行1次可以定義為：repeat_interval => 'FREQ=HOURLY; INTERVAL=2;'
不能使用PL/SQL Expression方式指定：repeat_interval=> 'SYSTIMESTAMP + INTERVAL '2' HOUR'
Event型別的schedule在建立的時候使用CREATE_EVENT_SCHEDULE裡的queue_spec、event_condition兩個引數指定佇列名稱及佇列中的事件條件，但後續如果要使用set_attribute修改這兩個引數時，統一透過event_spec這一個引數進行修改，這個引數有value、value2兩個值，value對應queue_spec、value2對應event_condition
Event型別的schedule所使用的佇列如果是一個安全佇列的話必須以即將使用這個佇列的使用者執行DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER定義一個agent，或者以SYS使用者執行 DBMS_AQADM.ENABLE_DB_ACCESS也可以達到同樣效果
3. Window
Scheduler功能的延伸，在resource manager enabled的情況下能在window視窗開啟時切換到一個不同的resource_plan，從而使在該執行視窗執行的job能得到更合理的資源分配。
建立Window時duration屬性表示每一次視窗開啟的持續時間，最小單位為1分鐘，end_time屬性表示視窗到end_time指定的時間後不在開啟，即意味著window變為disabled，並不會強制將End_time之前已經開啟但持續時間尚未達到Duration指定值的window關閉。因此當End_time到來的時候仍有可能有window處於open狀態，比如下面的例子中W0214_1實際關閉的時間是sysdate+4/1440而不是end_time所定義的sysdate+2/1440：
dbms_scheduler.create_window(window_name=>'W0214_1',resource_plan=>NULL,start_date=>sysdate+1/1440,end_date=>sysdate+2/1440,duration=>interval '3' minute,repeat_interval=>NULL);
window overlap的情況要儘可能的避免，因為可能導致到了時間點job無法發起的現象；
window建立後預設就是enabled的，只有當前時間超過window定義的end_time、window所引用的schedule被drop掉的情況下window才會被disabled；
如果要讓window開啟時resource_plan不發生變化，必須指定alter system set resoure_manager_plan=force:plan名稱，強制系統保留在當前的resource_plan；
當window close時預設情況下不會停止在這個視窗發起的job，除非在建立job時指定了stop_on_window_close=TRUE，但要注意如果window在job完成前就關閉了，Resource plan就會還原到window開啟前的設定值，這樣可能使得剩餘的job步驟執行時分配不到應有的系統資源，可能引起job執行變慢；
4. Job
要使job能正常排程job_queue_processes一定不能設為0；
job執行內容有兩種方式定義：inline方式、program方式。
inline方式舉例如下：
dbms_scheduler.create_job(job_name=>'Job1',job_type=>'EXECUTABLE',job_action=>'/home/oracle/chh1.sh');
program方式舉例如下：
dbms_scheduler.create_program(program_name=>'P1',program_action=>'/home/oracle/chh1.sh',program_type=>'EXECUTABLE');
dbms_scheduler.create_job(job_name=>'Job1',program_name=>'Job1');
job發起的時間也有兩種定義方式：inline方式、named schedule方式
inline方式舉例如下：
dbms_scheduler.create_job(job_name=>'Job1',job_type=>'EXECUTABLE',job_action=>'/home/oracle/chh1.sh',start_date=>sysdate+1/1440,end_Date=>sysdate+60/1440,repeat_interval=>'FREQ=MINUTELY');
named schedule方式舉例如下：
dbms_scheduler.create_schedule(schedule_name=>'sched1',start_date=>sysdate+1/1440,end_Date=>sysdate+60/1440,repeat_interval=>'FREQ=MINUTELY');
dbms_scheduler.create_job(job_name=>'Job1',job_type=>'EXECUTABLE',job_action=>'/home/oracle/chh1.sh',schedule_name=>'sched1');
job建立後預設為Disable狀態，auto_drop屬性預設為TRUE，auto_drop=TRUE表示在job執行結束後自動drop掉，但有一種情況例外：當使用named schedule方式定義job的執行時間，且這個schedule是一個window，這種情況下即使auto_drop設為TRUE，job執行完成後也不會被drop掉，比如下面案例中建立出來的j131_3：
dbms_scheduler.create_window(window_name=>'w131_3',resource_plan=>NULL,start_Date=>systimestamp+1/1440,repeat_interval=>NULL,duration=>interval '1' minute);
dbms_scheduler.create_job(job_name=>'j131_3',schedule_name=>'sys.w131_3',job_type=>'PLSQL_BLOCK',job_action=>'insert into t131 values(''j131_3'',systimestamp);commit;',enabled=>TRUE,auto_drop=>TRUE);
dba_scheduler_jobs.state為completed和succeeded間的區別是，completed出現在repeat_interval不為空值的job最後一次被排程執行完成之後，表示這個job所有的排程已經完成。Succeeded只會出現在 repeat_interval為空值、即run-once 的job完成之後；
job在建立之後的預設狀態為disabled，除此之外在其所依賴的物件比如program被強行drop掉、在job的排程週期結束後均會被置為disabled狀態；
job建立時其logging_level預設為OFF，即job執行時不記錄任何日誌，但是我們從dba_scheduler_job_log等卻能看到job的日誌，是因為job預設會分配給名為DEFAULT_JOB_CLASS的job class而這個job class的logging_level是RUNS—表示記錄執行期間的日誌；
使用local/remote external job執行shell指令碼的時候一定要在shell指令碼里加上Shell直譯器，例如：#!/bin/sh，也要在指令碼里設定好要用到的環境變數，例如：export TZ=BEIST-8
對event-based job來說設定parallel_instances=TRUE能夠在前一次由event觸發的job還在執行時，又有新的event到來後再一次觸發job執行，能夠及時的響應每一次的event，使一個job的多個instances同時執行，需要注意的是這種情況下的job都是lightweight型別的，我們可以在dba_scheduler_job_log.job_subname列裡看到類似於SCHED$_EVTPARINST_X的名稱來代表一個lightweight job instance
如果A使用者要執行B使用者下的Lightweight job，那麼A使用者只要對B使用者下的這個lightweight job裡所指定的program具有執行許可權即可，這一點不同於regular job，如果B使用者下的是regular job那麼A必須對B使用者下的job也要具有執行許可權；
如果要讓job在資料庫從異常情況恢復後繼續執行，必須設定restartable=TRUE，但如果後續job執行還是失敗，且失敗次數超過6次後，job就會停止執行，直到下一次排程時間到來後才會再次發起執行
dbms_scheduler.run_job能夠在job狀態為disabled的情況下人工發起job執行，如果job型別為chain、job裡包含detached program、或者job是在遠端主機或資料庫上執行這三種情況下，必須在run_job時指定use_current_session=TRUE，比如dbms_scheduler.run_job(job_name=>'job1',use_current_session=>TRUE)，這時雖然是手工執行job，dba_scheduler_jobs檢視裡的run_count、last_start_date等欄位仍然會更新；
job_priority能在同一個job class裡的不同job間區別優先順序，不同job class裡的job間沒有優先順序高低之分。Job_priority在系統資源緊張的時候會保證同一個job class裡排程時間相同的兩個job，優先順序高的比優先順序低的先發起，如果系統資源比較空閒，則兩個job會在同一時間發起。
Set_job_argument_value只能用於傳入Varchar型別的引數值，其它型別的引數值可以使用set_job_anydata_value傳入，用法如下：
--假設傳入timestamp型別
declare
v_agvalue sys.anydata;
begin
v_agvalue:=sys.anydata.converttimestamp(systimestamp);
DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(job_name=>'LJ0213_3',argument_position=>1,argument_value=>v_agvalue);
end;
/
Enable job時會清空dba_scheduler_jobs裡的RUN_COUNT, FAILURE_COUNT , RETRY_COUNT欄位以重新開始計數；
5. Job Class
Job class統一建立在SYS使用者下，將同一類的job組合在一起，為這個class裡的所有job指定resource group，使job能分配到足夠執行資源，在RAC環境下還能指定執行在哪個service上以實現在不同RAC節點上執行不同類別job的功能；
當job class的logging_level和單個job設定的logging_level取值不同時，記錄資訊多的那個設定生效
6. Chain
多個program的集合，每個program稱為一個step，透過rule來定義這些step的執行順序，要使得chain能夠開始執行，必須有一個rule的condition設定為TRUE。要是chain能夠正常結束，必須有一個rule的action為'END'，否則dba_scheduler_jobs裡的state將顯示為CHAIN_STALLED，表示chain處於既沒有step在執行也沒有正常結束的狀態；
Create_chain裡的Evaluation_interval引數，是一個比較有用的選項，能夠對chain 定義之外的物件進行定時監測，如果滿足條件則可以觸發某個step執行，例如下面的定義表示每1分鐘監測一次trigtab表的記錄數，如果大於0且step ST0210_6成功那麼開始執行ST0210_5
dbms_scheduler.create_chain(chain_name=>'cha0210_5',rule_set_name=>NULL,evaluation_interval=>interval '1' minute);
dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_5',program_name=>'P0210_5');
dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_6',program_name=>'P0210_6');
dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>':ST0210_6.state=''SUCCEEDED'' and (select count(*) from trigtab) > 0',action=>'start ST0210_5',rule_name=>'R0210_62');
使用ALTER_RUNNING_CHAIN對正在執行的chain job裡的step進行屬性修改，需要對chain job的修改許可權即可，無須對chain本身具有修改許可權；
一個chain的完整執行過程應包含chain_start(running)->chain_run(succeeded)->chain(completed)，下面是從dba_scheduler_job_log裡獲取的一個chain從開始到結束的執行狀態變化過程
select job_name,log_date,operation,status from dba_scheduler_job_log where job_name='J0210_5' order by log_date desc;
JOB_NAME LOG_DATE OPERATION STATUS
-------- -------------------------------------------------- --------------- --------------------
J0210_5 12-FEB-15 10.50.45.205651 AM +08:00 COMPLETED
J0210_5 12-FEB-15 10.50.45.205013 AM +08:00 CHAIN_RUN SUCCEEDED
J0210_5 12-FEB-15 10.50.45.110554 AM +08:00 RUN SUCCEEDED
J0210_5 12-FEB-15 10.50.45.006277 AM +08:00 CHAIN_START RUNNING
Chain running和start的特點
7. Credential
將OS、DB層面的使用者名稱和口令定義到訪問身份證明裡之後對於執行local/remote external jobs和remote database jobs時可以直接使用這個身份證明。對於local database jobs執行使用的身份是job owner，所以不需要定義額外的credential
8. Destination
remote database/external jobs執行在哪臺主機或者資料庫上，必須在remote主機上必須安裝scheduler agent，安裝方法可以參照在”oracle 11g下安裝配置scheduler agent”:http://blog.itpub.net/53956/viewspace-1436615/，完成agent註冊後，預設就建立好了external destination，可以從dba_scheduler_external_dests裡看到destination名稱，但是這樣僅滿足執行remote external jobs的條件，要執行remote database jobs還必須在job主資料庫上用create_database_destination建立出database destination；
建立remote jobs時一般同時指定credential_name作為預設的credential，比如：
dbms_scheduler.create_credential(credential_name=>'C0217_2',username=>'autotest',password=>'crm_2013');
dbms_scheduler.create_job(job_name=>'J0217_1',job_action=>'PRC0217_1',job_type=>'STORED_PROCEDURE',end_Date=>systimestamp+300/1440,auto_Drop=>FALSE,destination_name=>'D0217_1',credential_name=>'C0217_2');
如果上面不指定credential_name，那麼必須在destination_name前加上credential_name字首：
dbms_scheduler.create_job(job_name=>'J0217_1',job_action=>'PRC0217_1',job_type=>'STORED_PROCEDURE',end_Date=>systimestamp+300/1440,auto_Drop=>FALSE,destination_name=>' C0217_2.D0217_1');
9. File Watcher
用來探測本地或遠端主機指定目錄下是否有新檔案生成，如果有插入一條事件訊息到佇列中，基於file watcher的job收到這條訊息後便會自動開始執行。去目錄下探測是否有新檔案生成的工作由SYS使用者下名為FILE_WATCHER_SCHEDULE的job來完成，預設時每隔10分鐘探測一次，這一時間間隔可以使用如下方法進行調整： dbms_scheduler.set_attribute('SYS.FILE_WATCHER_SCHEDULE','repeat_interval','FREQ=MINUTELY');最小可以調整為每分鐘1次，訪問sys.aq$scheduler_filewatcher_qt表可以檢視已經入到佇列的訊息內容；
基於file watcher的job在其Parallel_instances屬性為FALSE(預設值)的情況下永遠只處理時間戳最新的一個檔案，假設FILE_WATCHER_SCHEDULE的時間間隔為1分鐘，那麼一分鐘內目錄下陸續到達了3個檔案只有時間最新的那個檔案會被處理，其它兩個會被忽略，如果要一個不落的逐個處理必須將parallel_instances設為TRUE，這樣會啟動多個instances並行處理每個檔案；
10. Group
可以為window、destination建立group，使用window group、destination group可以在建立job時為job同時定義包含多個Destination的destination group作為其destination_name，也可以定義包含多個window的window group作為其schedule_name，省去了針對單個destination或window進行逐一定義的繁瑣
第二部分：Oracle scheduler裡的許可權
除非建立時將名稱放在雙引號裡表示嚴格區分大小，否則建立出來的oracle schedule物件名稱都是以大寫形式存放在資料字典裡的。
job class、window、型別為window 的group三類物件其schema都是sys，當引用window和型別為window的group時必須在名稱前加上SYS。其它物件都是建立在哪個schema下就屬於哪個schema。
Job、 chain、program三類物件建立完之後預設均為disabled狀態，其餘物件建完後都是立即可用的狀態
Schedule、destination、window、group(window型別)四類物件的使用許可權是grant給public的，所有使用者都能使用，比如A使用者建完一個名為DestA的Destination，B使用者不用被授予DestA的任何物件許可權也能使用DestA
對於系統許可權的使用歸納如下：
|
System privilege |
System privilege能做什麼 |
|
Create job |
在自己的schema下建立job、chain、schedule、program、file_watcher、credential、destination、group |
|
無需任何許可權 |
修改和刪除 自己schema下的job、chain、schedule、program、file_watcher、credential、destination、group |
|
Create any job |
在別的schema下（SYS除外）建立、修改、執行、刪除job、chain、schedule、program、file_watcher、credential、destination、group， |
|
Create external job |
建立job_action=Executable的job或者指向的program_type=executable型別的job |
|
Create job Create external job |
執行external job |
|
Execute any program |
有權使用任何使用者下的program |
|
Execute any class |
有權使用SYS使用者下的所有job class |
|
Manage scheduler |
建立/修改/刪除 job class、window、group(window型別) Stop_job(job_name=>’job1’,force=>TRUE)—強行終止job Set_scheduler_attribute –修改scheduler全域性屬性 Purge_log--清理Scheduler log set_agent_registration_pass –設定schedule agent連線口令 |
關於物件許可權的使用歸納如下
|
Scheduler object |
Operation |
Object privilege |
|
Job |
A使用者執行/修改/刪除B使用者下的job |
grant alter on B.jobname to A |
|
Program |
A使用者使用 B使用者下的program |
grant execute on B.program_name to A |
|
A使用者修改/刪除B使用者下的program |
grant alter on B.program_name to A |
|
|
chain |
A使用者使用 B使用者下的chain |
grant execute on B.chain_name to A |
|
A使用者修改/刪除B使用者下的chain |
grant alter on B.chain_name to A |
|
|
File watcher |
A使用者使用 B使用者下的File watcher |
grant execute on B.file_watcher_name to A |
|
A使用者修改/刪除B使用者下的File watcher |
grant alter on B. file_watcher_name to A |
|
|
Credential |
A使用者使用 B使用者下的Credential |
grant execute on B.credential_name to A |
|
A使用者修改/刪除B使用者下的Credential |
grant alter on B.credential_name to A |
|
|
Destination |
A使用者使用B使用者下的Destination |
無需額外賦權 |
|
A使用者修改/刪除B使用者下的Destination |
grant alter on B.destination_name to A |
|
|
Job class |
A使用者使用SYS使用者下的Job class |
grant execute on sys. Job_class_name to A |
|
A使用者修改/刪除SYS使用者下的Job class |
grant Manage scheduler to A |
|
|
Schedule |
A使用者使用B使用者下的Schedule |
無需額外賦權 |
|
A使用者修改/刪除B使用者下的Schedule |
grant alter on B.Schedule_name to A |
|
|
Window |
A使用者使用SYS使用者下的window |
無需額外賦權 |
|
A使用者修改/刪除SYS使用者下的window |
grant Manage scheduler to A |
|
|
Group(window型別) |
A使用者使用SYS使用者下的group(window型別) |
無需額外賦權 |
|
A使用者修改/刪除SYS使用者下的group(window型別) |
grant Manage scheduler to A |
|
|
Group(destination型別) |
A使用者使用B使用者下的Group(destination型別) |
grant select on group_name to B; |
|
A使用者修改/刪除B使用者下的Group(destination型別) |
grant alter on group_name to B; |
第三部分：一些有助於概念理解的小測試
///////////////////////////////////////////////////////////////////////////////////////////////
/// 1、11.2版本開始job_queue_processes=0能夠禁用cjq程式，當cjq程式被禁用時任何排程都無法啟動
////////////////////////////////////////////////////////////////////////////////////////////
11.2版本以前設定job_queue_processes=0，不會禁止排程啟動，從11.2開始job_queue_processes=0會使得任何排程都無法啟動，因此設定job_queue_processes!=0是確保排程正常執行的首要前提
####設定job_queue_processes=0禁用cjq程式
***job_queue_processes!=0時，ora_cjq0_tstdb1程式存在
tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1 | grep cjq
tstdb1 43778098 50725024 0 21:30:21 pts/0 0:00 grep cjq
tstdb1 7734570 1 0 Jan 11 - 5:07 ora_cjq0_tstdb1
SQL> show parameter job_queue_process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
***設定job_queue_processes=0後發現cjq程式不在了
SQL> alter system set job_queue_processes=0 scope=memory;
System altered.
SQL> show parameter job_queue_process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1 | grep cjq
tstdb1 5505512 7996032 0 21:32:33 pts/0 0:00 grep cjq
***在alert.log裡可以看到job_queue_processes被修改的同時,cjq程式被終止
Wed Jan 28 21:32:18 2015
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;
Wed Jan 28 21:32:18 2015
Stopping background process CJQ0
####設定job_queue_processes=1000重新啟用cjq程式
***設定job_queue_processes=1000
SQL> alter system set job_queue_processes=1000 scope=memory;
System altered.
***立即檢視發現cjq程式沒有立即啟動
tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1 | grep cjq
tstdb1 5505512 7996032 0 21:32:33 pts/0 0:00 grep cjq
***從alert.log裡看到過了約4分鐘後cjq程式啟動
Wed Jan 28 21:39:58 2015
ALTER SYSTEM SET job_queue_processes=1000 SCOPE=MEMORY;
Wed Jan 28 21:43:54 2015
Starting background process CJQ0
Wed Jan 28 21:43:54 2015
CJQ0 started with pid=38, OS id=9634156
***OS層面驗證cjq程式的啟動時間與alert.log的啟動時間一致
oracle@jq570322b:/home/oracle>ps -ef|grep tstdb1 | grep cjq
tstdb1 9634156 1 0 21:43:54 - 0:00 ora_cjq0_tstdb1
小提示：cjq0雖是後臺程式，但它並不總是常駐的，在沒有job執行的情況下會自動退出，在有job即將執行的情況下會自動重啟，cjq0程式停止、啟動的行為alert.log裡有所體現，例如
Tue Feb 03 22:29:57 2015
Stopping background process CJQ0
Tue Feb 03 22:44:51 2015
Starting background process CJQ0
我們可以很容易的模擬出上述情況：將cjq0程式先kill掉，alert.log立即顯示Stopping background process CJQ0
然後設定一個即將open的window，在window open前的幾秒鐘就能在alert.log裡看到Starting background process CJQ0的資訊
//////////////////////
// 2、dba_scheduler_global_attribute.CURRENT_OPEN_WINDOW能看出當前哪個視窗處於active
//////////////////////
SQL> select * from dba_scheduler_global_attribute;
ATTRIBUTE_NAME VALUE
------------------------------ --------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY 30
DEFAULT_TIMEZONE
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT 0
CURRENT_OPEN_WINDOW WEDNESDAY_WINDOW
11 rows selected.
///////////////////////////////////
// 3、dba_scheduler_job_log、dba_scheduler_job_run_details內容上的區別
// dba_Scheduler_window_log、dba_Scheduler_window_details內容上的區別
///////////////////////////////////
###dba_scheduler_job_log、dba_scheduler_job_run_details內容上的區別
***當前時間為週三14:00，下面的語句設定WEDNESDAY_WINDOW視窗的open時間為14:50
exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=14;byminute=50; bysecond=0');
***sql tuning advisor自動任務將在14:50發起
col window_next_time format a40
set linesize 140
select * from dba_autotask_window_clients where window_name='WEDNESDAY_WINDOW';
WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ ---------------------------------------- ----- -------- -------- -------- -------- --------
WEDNESDAY_WINDOW 28-JAN-15 14.50.00.000000 PM +08:00 TRUE ENABLED ENABLED ENABLED ENABLED DISABLED
***1個小時左右,sql_tuning_advisor自動任務結束後，觀察dba_scheduler_job_log和dba_scheduler_job_run_details
col job_name format a25
col job_name format a20
col operation format a10
col log_date format a40
set linesize 167
select job_name,job_class,log_date,operation from dba_scheduler_job_log where job_name='ORA$AT_SQ_SQL_SW_817' order by log_date;
JOB_NAME JOB_CLASS LOG_DATE OPERATION
-------------------- ------------------------------ ---------------------------------------- ----------
ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ 28-JAN-15 02.55.00.797278 PM +08:00 UPDATE
ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ 28-JAN-15 02.55.00.807214 PM +08:00 ENABLE
ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ 28-JAN-15 03.55.05.780034 PM +08:00 RUN
ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ 28-JAN-15 03.55.05.780947 PM +08:00 COMPLETED
ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ 28-JAN-15 03.55.05.781699 PM +08:00 DROP
SQL> select job_class_name,RESOURCE_CONSUMER_GROUP,LOGGING_LEVEL from dba_scheduler_job_classes where job_class_name='ORA$AT_JCNRM_SQ';
JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP LOGGING_LEV
------------------------------ ------------------------------ -----------
ORA$AT_JCNRM_SQ ORA$AUTOTASK_SQL_GROUP FULL
col job_name format a30
col status format a10
col log_date format a30
col actual_start_date format a30
col run_duration format a16
select job_name,log_date,status,actual_start_Date,run_duration from dba_scheduler_job_run_details where job_name='ORA$AT_SQ_SQL_SW_817' order by log_date;
JOB_NAME LOG_DATE STATUS ACTUAL_START_DATE RUN_DURATION
------------------------------ ------------------------------ ---------- ------------------------------ ----------------
ORA$AT_SQ_SQL_SW_817 28-JAN-15 03.55.05.780501 PM + SUCCEEDED 28-JAN-15 02.55.02.707063 PM + +000 01:00:03
08:00 08:00
sql tuning advisor自動任務執行時派生出的job名為AT_SQ_SQL_SW_817，AT_SQ_SQL_SW_817歸屬於名為ORA$AT_JCNRM_SQ的job classes，ORA$AT_JCNRM_SQ的log_level為FULL，所以我們看到在dba_scheduler_job_log裡記錄了所有關於job ORA$AT_SQ_SQL_SW_817的操作記錄，包括update->enable->run->completed->drop五個階段，其中update對應我們設定Schedule的操作：exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=14;byminute=50; bysecond=0'); enable操作是因為我們在set_attribute的時候系統會自動把WEDNESDAY_WINDOW先置為disable，修改完attribute後，系統重新enable WEDNESDAY_WINDOW，disable和enable的操作都是oracle自動完成的，run->completed->drop三個階段反應了job執行、job執行完成後把自己drop掉的過程。而在dba_scheduler_job_run_details裡只有一行有關ORA$AT_SQ_SQL_SW_817執行結果的記錄，這條記錄只有在ORA$AT_SQ_SQL_SW_817執行完成後才能看到，並且包含了實際執行時間、執行時長等較為豐富的時間明細
###在執行上述sql tuning advisor自動任務的過程中dba_Scheduler_window_log、dba_Scheduler_window_details內容上的區別
select log_date,window_name,operation from dba_scheduler_window_log order by log_date;
LOG_DATE WINDOW_NAME OPERATION
---------------------------------------- ----------------------------------------------------------------- ----------
28-JAN-15 02.53.22.222563 PM +08:00 WEDNESDAY_WINDOW UPDATE
28-JAN-15 02.55.00.644519 PM +08:00 WEDNESDAY_WINDOW OPEN
28-JAN-15 06.55.00.018390 PM +08:00 WEDNESDAY_WINDOW CLOSE
col log_Date format a30
col req_start_date format a30
col actual_start_date format a30
col actual_duration format a30
col window_duration format a30
col window_name format a10
set linesize 150
set pagesize 200
select log_date,window_name,req_start_date,actual_start_date,window_duration,actual_duration from Dba_Scheduler_Window_Details where window_name='WEDNESDAY_WINDOW' order by log_date desc;
LOG_DATE WINDOW_NAM REQ_START_DATE ACTUAL_START_DATE WINDOW_DURATION
------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
ACTUAL_DURATION
------------------------------
28-JAN-15 06.55.00.018998 PM + WEDNESDAY_ 28-JAN-15 02.55.00.000000 PM + 28-JAN-15 02.55.00.302413 PM + +000 04:00:00
08:00 WINDOW 08:00 08:00
+000 04:00:00
dba_scheduler_window_log裡也包含了三個步驟update(更新WEDNESDAY_WINDOW屬性)->open->close，這裡的close時間總是要大於等於AT_SQ_SQL_SW_817 job完成的時間。與dba_scheduler_job_run_details類似，Dba_Scheduler_Window_Details只含有一條記錄，且只有等window close後才會出現，表示了整個視窗的時間跨度。
dba_scheduler_job_run_details.ADDITIONAL_INFO欄位裡顯示了job執行出錯的完整原因，ERROR裡顯示了ORA錯誤號
///////////////////////////////////
// 4、哪些檢視可以反映出autotask的執行過程與執行結果
///////////////////////////////////
執行過程：
select * from dba_autotask_client_job;
select * from v$advisor_progress;
執行結果：
dba_autotask_client_history;
dba_autotask_job_history;
dba_scheduler_job_log;
dba_scheduler_job_run_details;
///////////////////////////////////
// 5、stop_on_window_close=TRUE/FALSE決定了window close的時候job是否會終止
///////////////////////////////////
###stop_on_window_close=TRUE，close window後job也隨之停止
--建立測試表
create table t0202_2 (c1 varchar2(10),tm timestamp);
--create job
create or replace procedure prc0202_2
is
begin
while ( true )
loop
insert into t0202_2 values('J0202_2',systimestamp);
dbms_lock.sleep(5);
commit;
end loop;
end;
/
truncate table t0202_2;
exec dbms_scheduler.drop_job(job_name=>'J0202_2');
exec dbms_scheduler.drop_window(window_name=>'w0202_2',force=>TRUE);
exec dbms_Scheduler.drop_schedule(schedule_name=>'s0202_2');
exec dbms_Scheduler.purge_log(job_name=>'J0202_2');
exec dbms_scheduler.drop_program(program_name=>'P0202_2');
exec dbms_scheduler.create_program(program_name=>'P0202_2',program_type=>'STORED_PROCEDURE',program_action=>'prc0202_2');
exec dbms_scheduler.create_schedule(schedule_name=>'s0202_2',start_date=>systimestamp+1/1440,repeat_interval=>NULL,end_date=>systimestamp+10/1440);
exec dbms_scheduler.create_window(window_name=>'w0202_2',resource_plan=>'MIXED_WORKLOAD_PLAN',schedule_name=>'s0202_2',duration=>interval '10' minute);
exec dbms_scheduler.create_job(job_name=>'J0202_2',program_name=>'P0202_2',schedule_name=>'sys.w0202_2',auto_drop=>FALSE);
exec dbms_scheduler.set_attribute('J0202_2','stop_on_window_close',TRUE);
exec dbms_scheduler.enable('P0202_2,J0202_2');
--W0202_2處於open狀態
select * from dba_scheduler_global_attribute where attribute_name='CURRENT_OPEN_WINDOW';
ATTRIBUTE_NAME VALUE
------------------------------ --------------------
CURRENT_OPEN_WINDOW W0202_2
--5秒鐘一次有記錄進入t0202_2，說明job在正常執行
select count(*) from t0202_2
COUNT(*)
----------
1
select count(*) from t0202_2
COUNT(*)
----------
2
select count(*) from t0202_2
COUNT(*)
----------
3
--close掉w0202_2後，t0202_2表記錄停止更新
exec dbms_scheduler.close_window(window_name=>'w0202_2');
select count(*) from t0202_2
COUNT(*)
----------
6
select count(*) from t0202_2
COUNT(*)
----------
6
select count(*) from t0202_2
COUNT(*)
----------
6
--dba_Scheduler_job_run_details顯示J0202_2停止的原因是"Stop job called because associated window was closed"
col job_name format a20
col status format a30
col additional_info format a80
set linesize 170
select job_name,additional_info,status from dba_Scheduler_job_run_details where job_name='J0202_2';
JOB_NAME ADDITIONAL_INFO STATUS
-------------------- -------------------------------------------------------------------------------- ------------------------------
J0202_2 REASON="Stop job called because associated window was closed" STOPPED
###stop_on_window_close=FALSE，close window後job繼續執行
--在上述測試的基礎上，設定stop_on_window_close=FALSE
truncate table t0202_2;
exec dbms_scheduler.set_attribute('s0202_2','start_date',systimestamp+1/1440);
exec dbms_Scheduler.set_attribute('s0202_2','end_date',systimestamp+10/1440);
exec dbms_Scheduler.enable('sys.W0202_2');
exec dbms_Scheduler.set_attribute('j0202_2','stop_on_window_close',FALSE);
--W0202_2視窗開啟時的狀態
col value format a40
set linesize 120
select * from dba_scheduler_global_attribute where attribute_name='CURRENT_OPEN_WINDOW';
ATTRIBUTE_NAME VALUE
------------------------------ ----------------------------------------
CURRENT_OPEN_WINDOW W0202_2
SQL> select count(*) from t0202_2;
COUNT(*)
----------
10
SQL> select count(*) from t0202_2;
COUNT(*)
----------
11
--close window
SQL> exec dbms_Scheduler.close_window('W0202_2');
PL/SQL procedure successfully completed.
SQL> select * from dba_scheduler_global_attribute where attribute_name='CURRENT_OPEN_WINDOW';
ATTRIBUTE_NAME VALUE
------------------------------ --------------------
CURRENT_OPEN_WINDOW
--t0202_2記錄仍然在增長
SQL> select count(*) from t0202_2;
COUNT(*)
----------
31
SQL> select count(*) from t0202_2;
COUNT(*)
----------
32
///////////////////////////////////
// 6、define_program_argument用於inline方式定義job_action和非inline方式定義job_action的job，這兩種情況下有何區別
///////////////////////////////////
create or replace procedure prc0204_2 (v_info in varchar2)
as
begin
dbms_output.put_line(v_info);
end;
/
###inlined program方式定義job_action，job_type='STORED_PROCEDURE'的情況
exec dbms_scheduler.create_job(job_name=>'J0204_2',job_type=>'STORED_PROCEDURE',job_action=>'prc0204_2',number_of_arguments=>1);
exec dbms_scheduler.set_job_argument_value(job_name=>'J0204_2',argument_position=>1,argument_value=>'aaa');
set serveroutput on
SQL>exec dbms_scheduler.run_job('J0204_2');
aaa
###非inlined program方式定義job_action，program_type='STORED_PROCEDURE'的情況，多了一步define_program_argument，且非inline方式可以利用default_value指定預設值，亦能指定argument_name
exec dbms_scheduler.create_program(program_name=>'P0204_2',program_action=>'PRC0204_2',program_type=>'STORED_PROCEDURE',number_of_arguments=>1);
exec dbms_scheduler.define_program_argument(program_name=>'P0204_2',argument_position=>1,argument_type=>'varchar2',default_value=>'aaa');
exec dbms_scheduler.create_job(job_name=>'J0204_3',program_name=>'P0204_2');
exec dbms_scheduler.enable('P0204_2');
set serveroutput on
exec dbms_scheduler.set_job_argument_value(job_name=>'J0204_3',argument_position=>1,argument_value=>'bbb');
SQL> exec dbms_scheduler.run_job('J0204_3');
bbb
---不指定引數值，則輸出預設值
exec dbms_scheduler.RESET_JOB_ARGUMENT_VALUE(job_name=>'J0204_3',argument_position=>1);
set serveroutput on
SQL> exec dbms_scheduler.run_job('J0204_3');
aaa
PL/SQL procedure successfully completed.
###inlined program方式定義的job_type=>'PLSQL_BLOCK'的job，以及非inlined program方式定義的program_type='PLSQL_BLOCK'的job，無法使用define_program_argument&define_metadata_argument來定義引數，卻可以直接使用define_metadata_argument裡metadata_attribute中除了event_message以外欄位，包括job_name、job_start等，舉個例子，這個例子屬於"非inlined program方式定義的program_type='PLSQL_BLOCK'的job"
exec dbms_scheduler.drop_job(job_name=>'J0204_7');
exec dbms_scheduler.drop_program(program_name=>'P0204_7');
exec dbms_scheduler.create_program(program_name=>'P0204_7',program_action=>'BEGIN dbms_output.put_line(job_name||'':''||job_start);end;',program_type=>'PLSQL_BLOCK');
exec dbms_scheduler.create_job(job_name=>'J0204_7',program_name=>'P0204_7');
exec dbms_scheduler.enable('P0204_7');
set serveroutput on
SQL> exec dbms_scheduler.run_job('J0204_7');
J0204_7:05-FEB-15 01.08.55.145163 PM +08:00
PL/SQL procedure successfully completed.
在上面的輸出中我們位元組在plsql block裡引用了job_name、job_start 兩個metadata欄位，輸出了job的名稱和執行開始時間
///////////////////////////////////
// 7、如果遇到program裡的引數不是varchar2型別的，那麼必須使用SET_JOB_ANYDATA_VALUE來賦值
///////////////////////////////////
exec dbms_Scheduler.drop_job('LJ0213_3');
exec dbms_Scheduler.drop_program('P0213_3');
--建立procedure、program、job等物件
create or replace procedure PRC0213_3(v_str in timestamp)
as
begin
dbms_output.put_line(to_char(v_str,'yyyymmdd hh24:mi:ss.FF'));
dbms_lock.sleep(1);
end;
/
exec dbms_Scheduler.create_program(program_name=>'P0213_3',program_type=>'STORED_PROCEDURE',program_action=>'PRC0213_3',number_of_arguments=>1);
--exec dbms_Scheduler.define_program_argument(program_name=>'P0213_3',argument_position=>1,argument_type=>'VARCHAR2');
exec DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name=>'P0213_3',argument_position=>1,argument_type=>'timestamp');
exec dbms_Scheduler.enable('P0213_3');
exec dbms_Scheduler.create_job(job_name=>'LJ0213_3',program_name=>'P0213_3',job_style=>'regular',auto_Drop=>FALSE);
---設定sys.anydata型別的變數，作為入參傳入SET_JOB_ANYDATA_VALUE
declare
v_agvalue sys.anydata;
begin
v_agvalue:=sys.anydata.converttimestamp(systimestamp);
DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(job_name=>'LJ0213_3',argument_position=>1,argument_value=>v_agvalue);
end;
/
---執行job
set serveroutput on
exec dbms_Scheduler.run_job('LJ0213_3');
20150213 12:12:45.601728000
PL/SQL procedure successfully completed.
///////////////////////////////////
// 8、建立一個detached job
///////////////////////////////////
使用detached job關鍵在於使用end_detached_job_run把detached job的執行結果通知主job
---建立測試表
create table t0204_4 (c1 timestamp);
---建立procedure，procedure裡沒有包含通知主job的步驟
create or replace procedure prc0204_4
as
begin
insert into t0204_4 values(systimestamp);
commit;
--DBMS_SCHEDULER.END_DETACHED_JOB_RUN(job_name=>'J0204_4',error_number=>0,additional_info=>'detached job is successful'); -->應該在這裡呼叫END_DETACHED_JOB_RUN，以通知主job是否完成，這裡故意不加看下job執行後的效果
end;
/
--建立program、job
exec dbms_scheduler.create_program(program_name=>'P0204_5',program_type=>'STORED_PROCEDURE',program_action=>'PRC0204_4');
exec dbms_scheduler.set_attribute('P0204_5','detached',TRUE);
exec dbms_scheduler.create_job(job_name=>'J0204_4',program_name=>'P0204_5');
exec dbms_scheduler.enable('P0204_5');
---如果用run_job執行一個detached job，必須指定use_current_session=FALSE
exec dbms_scheduler.run_job('J0204_4',use_current_session=>FALSE);
SQL> select * from t0204_4;
C1
---------------------------------------------------------------------------
04-FEB-15 02.52.01.371199 PM
---檢視job狀態，因為program裡沒有執行END_DETACHED_JOB_RUN，所以job依然是running狀態
col job_name format a10
col program_name format a10
col state format a20
col last_start_date format a40
col last_run_duration format a40
set linesize 150
SQL> select job_name,program_name,state,last_start_date,last_run_duration from dba_scheduler_jobs where job_name='J0204_4';
JOB_NAME PROGRAM_NA STATE LAST_START_DATE LAST_RUN_DURATION
---------- ---------- -------------------- ---------------------------------------- ----------------------------------------
J0204_4 P0204_5 RUNNING 04-FEB-15 02.52.01.339706 PM +08:00
---手動執行END_DETACHED_JOB_RUN後dba_scheduler_job_log等檢視反應出J0204_4已經正常結束
exec DBMS_SCHEDULER.END_DETACHED_JOB_RUN(job_name=>'J0204_4',error_number=>0,additional_info=>'detached job is successful');
col job_name format a10
col program_name format a10
col state format a20
col last_start_date format a40
col last_run_duration format a40
set linesize 150
SQL> select job_name,program_name,state,last_start_date,last_run_duration from dba_scheduler_jobs where job_name='J0204_4';
JOB_NAME PROGRAM_NA STATE LAST_START_DATE LAST_RUN_DURATION
---------- ---------- -------------------- ---------------------------------------- ----------------------------------------
J0204_4 P0204_5 DISABLED 04-FEB-15 02.52.01.339706 PM +08:00 +000000000 00:14:25.815847
col log_date format a30
col owner format a10
col job_name format a10
col operation format a10
col status format a10
set linesize 120
select log_date,job_name,operation,status from dba_scheduler_job_log where job_name='J0204_4' order by log_date desc;
LOG_DATE JOB_NAME OPERATION STATUS
------------------------------ ---------- ---------- ----------
04-FEB-15 03.06.27.156039 PM + J0204_4 RUN SUCCEEDED
08:00
col job_name format a30
col status format a10
col log_date format a40
col actual_start_date format a40
col run_duration format a16
set linesize 150
select job_name,log_date,status,actual_start_Date,run_duration from dba_scheduler_job_run_details where job_name='J0204_4' order by log_date;
JOB_NAME LOG_DATE STATUS ACTUAL_START_DATE RUN_DURATION
------------------------------ ------------------------------ ---------- ------------------------------ ----------------
J0204_4 04-FEB-15 03.06.27.156913 PM +08:00 SUCCEEDED 04-FEB-15 02.52.01.339706 PM +08:00 +000 00:14:26
///////////////////////////////////
// 9、建立一個lightweight job
///////////////////////////////////
lightweight job的job_action必須定義為一個program_name，program_type必須為PLSQL_BLOCK或者STORED_PROCEDURE，lightweight job的物件許可權繼承於program的許可權：
grant connect,resource,create job to scott identified by "asdf3_14";
grant connect,resource,create job to scott2 identified by "asdf3_14";
sqlplus scott/773946
create table t0204_6 (c1 timestamp);
create or replace procedure prc0204_6
as
begin
insert into t0204_6 values(systimestamp);
commit;
end;
/
exec dbms_scheduler.create_program(program_name=>'P0204_6',program_action=>'PRC0204_6',program_type=>'STORED_PROCEDURE');
---對於lightweight job必須先enable program，再create_job
exec dbms_scheduler.enable('P0204_6');
exec dbms_scheduler.create_job(job_name=>'J0204_6',program_name=>'P0204_6',job_style=>'LIGHTWEIGHT');
select * from t0204_6;
---lightweight job不是Schema objects，因此在dba_object裡是查不到的
SQL> select count(*) from dba_objects where object_name='J0204_6';
COUNT(*)
----------
0
---正因為lightweight job不是Schema objects，所以scott2、scott3都有許可權執行J0204_6，當然scott2、Scott3使用者必須對J0204_6所呼叫的program具有執行許可權
sqlplus scott2/773946
grant execute on scott.P0204_6 to scott2;
exec dbms_scheduler.run_job(job_name=>'scott.J0204_6');
ORA-00600: internal error code, arguments: [kssadd: null parent], [], [], [],
上述問題正在提問metalink
///////////////////////////////////
// 10、建立一個chain
///////////////////////////////////
建立一個chain名為cha0205_1，其中包含以下一些物件：
J0205_0:這是一個job，該job成功完成後會生成events插入到SYS.SCHEDULER$_EVENT_QUEUE佇列，用於觸發後面名為est0205_1的event schedule
cha0205_1:這是一個chain，裡面包含了st0205_1、st0205_2、st0205_3、st0205_4四個chain step
est0205_1:這是一個基於event的schedule，由J0205_0成功完成後觸發
st0205_2，這是cha0205_1裡基於event的step，由名為est0205_1的event schedule觸發
st0205_3，這是cha0205_1裡的program step
st0205_4，這是cha0205_1裡的program step
st0205_1是cha0205_1裡的第一個步驟，當start st0205_1的時候，整個chain會等待est0205_1裡所包含的event發生，這裡的event具體就是J0205_0這個job是否成功完成，如果J0205_0能成功完成，那麼就會觸發est0205_1，進而使得st0205_1開始執行，這時整個chain才真正運轉起來，st0205_1的成功執行又會觸發st0205_2的執行，從st0205_2開始的執行邏輯是：
如果st0205_2執行失敗，則執行st0205_4，一旦st0205_4執行結束，無論結果是成功還是失敗，整個chain到此結束；
如果st0205_2執行成功，則執行st0205_3，一旦st0205_3執行結束，無論結果是成功還是失敗，整個chain到此結束；
---賦於ad使用者建立chain所需的許可權
grant create job,create rule,create evaluation context,create rule set to ad;
---ad使用者建立測試表
create table t0205_2 (c1 varchar2(100),c2 varchar2(100),tm timestamp);
create table t0205_34 (c1 varchar2(100),c2 varchar2(100),tm timestamp);
---建立program、以及program要用到的儲存過程
create or replace procedure PRC0205_2(v_jname in varchar2,v_jsubname in varchar2)
as
begin
insert into t0205_2 values(v_jname,v_jsubname,systimestamp);
commit;
end;
/
create or replace procedure PRC0205_34(v_jname in varchar2,v_jsubname in varchar2)
as
begin
insert into t0205_34 values(v_jname,v_jsubname,systimestamp);
commit;
end;
/
exec dbms_scheduler.create_program(program_name=>'P0205_2',program_action=>'PRC0205_2',program_type=>'STORED_PROCEDURE',number_of_arguments=>2);
exec dbms_scheduler.create_program(program_name=>'P0205_34',program_action=>'PRC0205_34',program_type=>'STORED_PROCEDURE',number_of_arguments=>2);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_2',metadata_attribute=>'job_name',argument_position=>1);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_2',metadata_attribute=>'job_subname',argument_position=>2);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_34',metadata_attribute=>'job_name',argument_position=>1);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_34',metadata_attribute=>'job_subname',argument_position=>2);
---建立chain
exec dbms_scheduler.create_chain(chain_name=>'cha0205_1',rule_set_name=>NULL,evaluation_interval=>NULL);
---在SYS.SCHEDULER$_EVENT_QUEUE佇列裡為ad使用者建立一個ad_agent用於存放所有ad使用者生成的Event
exec DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('ad_agent');
---建立event schedule，觸發條件是J0205_0成功完成
exec dbms_scheduler.create_event_schedule(schedule_name=>'est0205_1',start_date=>systimestamp+1/1440,end_date=>systimestamp+100/1440,event_condition=>'tab.user_data.event_type=''JOB_SUCCEEDED'' and tab.user_data.OBJECT_NAME=''J0205_0''',queue_spec=>'SYS.SCHEDULER$_EVENT_QUEUE,ad_agent');
---建立event step : st0205_1，st0205_1由Schedule:est0205_1觸發
exec dbms_scheduler.define_chain_event_step(chain_name=>'cha0205_1',step_name=>'st0205_1',event_schedule_name=>'est0205_1');
---建立event step : st0205_2、st0205_3、st0205_4
exec dbms_scheduler.define_chain_step(chain_name=>'cha0205_1',step_name=>'st0205_2',program_name=>'P0205_2');
exec dbms_scheduler.define_chain_step(chain_name=>'cha0205_1',step_name=>'st0205_3',program_name=>'P0205_34');
exec dbms_scheduler.define_chain_step(chain_name=>'cha0205_1',step_name=>'st0205_4',program_name=>'P0205_34');
---定義chain rule:r0205_1，僅當st0205_1.state='SUCCEEDED'時才啟動st0205_2
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>'TRUE',action=>'start st0205_1',rule_name=>'r0205_0');
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_1.state=''SUCCEEDED''',action=>'start st0205_2',rule_name=>'r0205_1');
---定義chain rule:r0205_2s，當st0205_2.state='SUCCEEDED'時啟動st0205_3
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_2.state=''SUCCEEDED''',action=>'start st0205_3',rule_name=>'r0205_2s');
---定義chain rule:r0205_2f，當st0205_2.state='FAILED'時啟動st0205_4
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_2.state=''FAILED''',action=>'start st0205_4',rule_name=>'r0205_2f');
---定義chain rule:r0205_3，當st0205_3.state='COMPLETED'時END
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_3.completed=''TRUE''',action=>'end',rule_name=>'r0205_3');
---定義chain rule:r0205_4，當st0205_4.state='COMPLETED'時END
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_4.completed=''TRUE''',action=>'end',rule_name=>'r0205_4');
---建立job_type=chain的J0205_7，每1分鐘執行一次
exec dbms_Scheduler.create_job(job_name=>'J0205_7',job_type=>'chain',job_action=>'cha0205_1',start_date=>systimestamp+1/1440,repeat_interval=>'systimestamp+interval ''1'' minute',end_date=>systimestamp+100/1440,auto_Drop=>FALSE);
---建立job_type=chain的J0205_0，併為J0205_0定義raised_event
exec dbms_Scheduler.create_job(job_name=>'J0205_0',job_type=>'PLSQL_BLOCK',job_action=>'BEGIN dbms_output.put_line(job_name||''executed'');END;',auto_Drop=>FALSE);
exec dbms_Scheduler.set_attribute('J0205_0','raise_events',DBMS_SCHEDULER.JOB_SUCCEEDED);
---enable除了J0205_0之外的其它物件，J0205_0準備透過run_job的方式手動執行，所以此處不必enable J0205_0
exec dbms_Scheduler.enable('P0205_2,P0205_34,cha0205_1,J0205_7');
---手工執行J0205_7,從dba_scheduler_jobs檢視發現J0205_7正處於running狀態，但其中的第一個step ST0205_1處在scheduled狀態，即正在等待
set serveroutput on
exec dbms_Scheduler.run_job('J0205_7',FALSE);
col last_start_date format a40
col next_run_date format a40
col repeat_interval format a40
set linesize 170
select job_name,job_subname,last_start_date,next_run_date,repeat_interval,enabled,state from dba_scheduler_jobs where job_name='J0205_7';
JOB_NAME JOB_SUBNAM LAST_START_DATE NEXT_RUN_DATE REPEAT_INTERVAL ENABL STATE
---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----- ----------
J0205_7 ST0205_1 TRUE SCHEDULED
J0205_7 08-FEB-15 08.52.23.719069 AM +08:00 08-FEB-15 08.53.11.000000 AM +08:00 systimestamp+interval '1' minute TRUE RUNNING
---對於job_subname='ST0205_1'的step可以清楚的看到它在等待SCHEDULER$_EVENT_QUEUE裡符合tab.user_data.event_type='JOB_SUCCEEDED' and tab.user_data.OBJECT_NAME='J0205_0'的事件
col job_name format a10
col job_subname format a10
col enabled format a10
col state format a10
col EVENT_QUEUE_NAME format a20
col EVENT_QUEUE_owner format a20
col EVENT_QUEUE_agent format a20
col event_condition format a20
set linesize 180
select job_name,job_subname,enabled,state,EVENT_QUEUE_NAME,EVENT_QUEUE_owner,EVENT_QUEUE_agent,event_condition from dba_scheduler_jobs where job_name='J0205_7' and job_subname='ST0205_1';
JOB_NAME JOB_SUBNAM ENABLED STATE EVENT_QUEUE_NAME EVENT_QUEUE_OWNER EVENT_QUEUE_AGENT EVENT_CONDITION
---------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------------
J0205_7 ST0205_1 TRUE SCHEDULED SCHEDULER$_EVENT_QUE SYS AD_AGENT tab.user_data.event_
UE type='JOB_SUCCEEDED'
and tab.user_data.O
BJECT_NAME='J0205_0'
---DBA_SCHEDULER_RUNNING_CHAINS裡也顯示四個step都沒有開始執行
col state format a20
select job_name,chain_name,state,step_name from DBA_SCHEDULER_RUNNING_CHAINS where chain_name='CHA0205_1';
JOB_NAME CHAIN_NAME STATE STEP_NAME
---------- ------------------------------ -------------------- ------------------------------
J0205_7 CHA0205_1 NOT_STARTED ST0205_3
J0205_7 CHA0205_1 NOT_STARTED ST0205_4
J0205_7 CHA0205_1 NOT_STARTED ST0205_1
J0205_7 CHA0205_1 NOT_STARTED ST0205_2
---兩張測試表均為空
SQL> select * from t0205_2;
no rows selected
SQL> select * from t0205_34;
no rows selected
---下面我們手動執行J0205_0
exec dbms_Scheduler.run_job('J0205_0',FALSE);
---dba_scheduler_jobs看到j0205_7的上次執行時間變為了08-FEB-15 08.56.36
col last_start_date format a40
col next_run_date format a40
col job_name format a10
col job_subname format a10
col operation format a10
col state format a10
set linesize 150
select job_name,job_subname,last_start_date,next_run_date,enabled,state from dba_scheduler_jobs where job_name='J0205_7';
JOB_NAME JOB_SUBNAM LAST_START_DATE NEXT_RUN_DATE ENABLED STATE
---------- ---------- ---------------------------------------- ---------------------------------------- ---------- ----------
J0205_7 ST0205_1 TRUE SCHEDULED
J0205_7 08-FEB-15 08.56.36.667627 AM +08:00 08-FEB-15 08.53.11.000000 AM +08:00 TRUE RUNNING
---DBA_SCHEDULER_RUNNING_CHAINS裡內容為空說明J0205_7前一次的執行已經結束後一次的排程尚未開啟，即當前沒有處於執行狀態顯示除了ST0205_4沒有被執行，其它三個step都成功執行過了
SQL> select job_name,chain_name,state,step_name,start_date,end_Date from DBA_SCHEDULER_RUNNING_CHAINS where chain_name='CHA0205_1';
no rows selected
---dba_scheduler_job_run_details有三個step成功執行以及整個chain成功完成的記錄
SQL> select log_date,job_name,job_subname,status from dba_scheduler_job_run_details where job_name='J0205_7' order by log_date desc;
LOG_DATE JOB_NAME JOB_SUBNAM STATUS
--------------------------------------------------------------------------- ---------- ---------- ----------
08-FEB-15 08.56.36.575874 AM +08:00 J0205_7 SUCCEEDED
08-FEB-15 08.56.36.468056 AM +08:00 J0205_7 ST0205_3 SUCCEEDED
08-FEB-15 08.56.36.295616 AM +08:00 J0205_7 ST0205_2 SUCCEEDED
08-FEB-15 08.56.36.090806 AM +08:00 J0205_7 ST0205_1 SUCCEEDED
---t0205_34表裡的資料表明確實是由ST0205_3插入的
col c1 format a20
col c2 format a20
col tm format a50
set linesize 100
select * from t0205_2;
C1 C2 TM
-------------------- -------------------- --------------------------------------------------
J0205_7 ST0205_2 08-FEB-15 08.56.36.292664 AM
select * from t0205_34;
C1 C2 TM
-------------------- -------------------- --------------------------------------------------
J0205_7 ST0205_3 08-FEB-15 08.56.36.465205 AM
---等待J0205_7下一分鐘排程發起,CHA0205_1處於執行態，ST0205_1~ST0205_4均處於not_started狀態，ST0205_1正在等待event的發生
select job_name,chain_name,state,step_name from DBA_SCHEDULER_RUNNING_CHAINS where chain_name='CHA0205_1';
JOB_NAME CHAIN_NAME STATE STEP_NAME
---------- ------------------------------ -------------------- ------------------------------
J0205_7 CHA0205_1 NOT_STARTED ST0205_3
J0205_7 CHA0205_1 NOT_STARTED ST0205_4
J0205_7 CHA0205_1 NOT_STARTED ST0205_1
J0205_7 CHA0205_1 NOT_STARTED ST0205_2
col job_name format a10
col job_subname format a10
col enabled format a10
col state format a10
col EVENT_QUEUE_NAME format a20
col EVENT_QUEUE_owner format a20
col EVENT_QUEUE_agent format a20
col event_condition format a20
set linesize 180
select job_name,job_subname,enabled,state,EVENT_QUEUE_NAME,EVENT_QUEUE_owner,EVENT_QUEUE_agent,event_condition from dba_scheduler_jobs where job_name='J0205_7' and job_subname='ST0205_1';
JOB_NAME JOB_SUBNAM ENABLED STATE EVENT_QUEUE_NAME EVENT_QUEUE_OWNER EVENT_QUEUE_AGENT EVENT_CONDITION
---------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------------
J0205_7 ST0205_1 TRUE SCHEDULED SCHEDULER$_EVENT_QUE SYS AD_AGENT tab.user_data.event_
UE type='JOB_SUCCEEDED'
and tab.user_data.O
BJECT_NAME='J0205_0'
---我們再執行一次J0205_0，便會再一次觸發J0205_7的執行
exec dbms_Scheduler.run_job('J0205_0',FALSE);
---08-FEB-15 09.08.52 J0205_7再次執行
SQL> select log_date,job_name,job_subname,status from dba_scheduler_job_run_details where job_name='J0205_7' order by log_date desc;
LOG_DATE JOB_NAME JOB_SUBNAM STATUS
--------------------------------------------------------------------------- ---------- ---------- ------------------------------
08-FEB-15 09.08.52.518654 AM +08:00 J0205_7 SUCCEEDED
08-FEB-15 09.08.52.422641 AM +08:00 J0205_7 ST0205_3 SUCCEEDED
08-FEB-15 09.08.52.262758 AM +08:00 J0205_7 ST0205_2 SUCCEEDED
08-FEB-15 09.08.52.067413 AM +08:00 J0205_7 ST0205_1 SUCCEEDED
08-FEB-15 08.56.36.575874 AM +08:00 J0205_7 SUCCEEDED
08-FEB-15 08.56.36.468056 AM +08:00 J0205_7 ST0205_3 SUCCEEDED
08-FEB-15 08.56.36.295616 AM +08:00 J0205_7 ST0205_2 SUCCEEDED
08-FEB-15 08.56.36.090806 AM +08:00 J0205_7 ST0205_1 SUCCEEDED
---next_run_Date顯示J0205_7下次的執行時間將會是08-FEB-15 09.09.52，但不知何故last_start_date並沒有更新為08-FEB-15 09.08.52
col last_start_date format a40
col next_run_date format a40
col job_name format a10
col job_subname format a10
col operation format a10
col state format a10
set linesize 150
select job_name,job_subname,last_start_date,next_run_date,enabled,state from dba_scheduler_jobs where job_name='J0205_7';
JOB_NAME JOB_SUBNAM LAST_START_DATE NEXT_RUN_DATE ENABLED STATE
---------- ---------- ---------------------------------------- ---------------------------------------- ---------- ----------
J0205_7 08-FEB-15 08.56.36.667627 AM +08:00 08-FEB-15 09.09.52.515966 AM +08:00 TRUE SCHEDULED
---08-FEB-15 09.08.52這波執行完之後，相隔1分鐘09.09.52 J0205_7再次發起，因為之後沒有執行J0205_0去觸發，所以chain一直處於空跑狀態
select log_date,job_name,job_subname,operation,status from dba_scheduler_job_log where job_name='J0205_7' order by log_date desc;
LOG_DATE JOB_NAME JOB_SUBNAM OPERATION STATUS
--------------------------------------------------------------------------- ---------- ---------- ------------- ------------------------------
08-FEB-15 09.09.52.535169 AM +08:00 J0205_7 CHAIN_START RUNNING
08-FEB-15 09.08.52.518294 AM +08:00 J0205_7 CHAIN_RUN SUCCEEDED
08-FEB-15 09.08.52.422237 AM +08:00 J0205_7 ST0205_3 RUN SUCCEEDED
08-FEB-15 09.08.52.262197 AM +08:00 J0205_7 ST0205_2 RUN SUCCEEDED
08-FEB-15 09.08.52.067038 AM +08:00 J0205_7 ST0205_1 RUN SUCCEEDED
08-FEB-15 08.56.36.669587 AM +08:00 J0205_7 CHAIN_START RUNNING
08-FEB-15 08.56.36.575523 AM +08:00 J0205_7 CHAIN_RUN SUCCEEDED
08-FEB-15 08.56.36.467629 AM +08:00 J0205_7 ST0205_3 RUN SUCCEEDED
08-FEB-15 08.56.36.295235 AM +08:00 J0205_7 ST0205_2 RUN SUCCEEDED
08-FEB-15 08.56.36.090405 AM +08:00 J0205_7 ST0205_1 RUN SUCCEEDED
以上走的是ST0205_1->ST0205_2->ST0205_3這條路徑，下面驗證一下ST0205_1->ST0205_2->ST0205_4這條路徑是否能走通，我們透過修改t0205_2表結構來實現：
---停止J0205_7
exec dbms_Scheduler.stop_job('J0205_7');
---為了不與之前的日誌混淆，先清理一下J0205_7的日誌，purge_log需要manage scheduler許可權
sqlplus '/ as sysdba'
grant manage scheduler to ad;
sqlplus ad/Uiop246!
exec dbms_scheduler.purge_log(log_history=>0,which_log=>'JOB_AND_WINDOW_LOG',job_name=>'J0205_7');
select count(*) from dba_scheduler_job_log where job_name='J0205_7';
COUNT(*)
----------
0
select count(*) from dba_scheduler_job_run_details where job_name='J0205_7';
COUNT(*)
----------
0
---修改表結構
truncate table t0205_2;
truncate table t0205_34;
alter table t0205_2 modify (tm number);
---在J0205_7新一輪發起之後，手動執行J0205_7
exec dbms_scheduler.run_job('J0205_7',FALSE);
col last_start_date format a40
col next_run_date format a40
col job_name format a10
col job_subname format a10
col operation format a10
col state format a10
set linesize 150
select job_name,job_subname,last_start_date,next_run_date,enabled,state from dba_scheduler_jobs where job_name='J0205_7';
JOB_NAME JOB_SUBNAM LAST_START_DATE NEXT_RUN_DATE ENABLED STATE
---------- ---------- ---------------------------------------- ---------------------------------------- ---------- ----------
J0205_7 ST0205_1 TRUE SCHEDULED
J0205_7 08-FEB-15 09.51.55.722624 AM +08:00 08-FEB-15 09.51.55.707219 AM +08:00 TRUE RUNNING
---執行J0205_0
exec dbms_scheduler.run_job('J0205_0');
---觀察dba_scheduler_jobs
col job_name format a10
col job_subname format a10
col enabled format a10
col state format a10
col EVENT_QUEUE_NAME format a20
col EVENT_QUEUE_owner format a20
col EVENT_QUEUE_agent format a20
col event_condition format a20
set linesize 180
select job_name,job_subname,enabled,state,EVENT_QUEUE_NAME,EVENT_QUEUE_owner,EVENT_QUEUE_agent,event_condition from dba_scheduler_jobs where job_name='J0205_7' and job_subname='ST0205_1';
no rows selected
---dba_scheduler_job_run_details內容推斷出當前執行的是ST0205_1->ST0205_2->ST0205_4
SQL> select log_date,job_name,job_subname,status from dba_scheduler_job_run_details where job_name='J0205_7' order by log_date desc;
LOG_DATE JOB_NAME JOB_SUBNAM STATUS
--------------------------------------------------------------------------- ---------- ---------- ------------------------------
08-FEB-15 09.54.04.544999 AM +08:00 J0205_7 SUCCEEDED
08-FEB-15 09.54.04.450601 AM +08:00 J0205_7 ST0205_4 SUCCEEDED
08-FEB-15 09.54.04.361981 AM +08:00 J0205_7 ST0205_2 FAILED
08-FEB-15 09.54.04.081797 AM +08:00 J0205_7 ST0205_1 SUCCEEDED
08-FEB-15 09.50.55.709786 AM +08:00 J0205_7 STOPPED
---ST0205_2 failed的原因是t0205_2表結構變更引起的PRC0205_2過程失效
col additional_info format a50
set linesize 140
select job_name,job_subname,status,additional_info from dba_scheduler_job_run_details where job_name='J0205_7' and job_subname='ST0205_2' order by log_date desc;
JOB_NAME JOB_SUBNAM STATUS ADDITIONAL_INFO
---------- ---------- ------------------------------ --------------------------------------------------
J0205_7 ST0205_2 FAILED CHAIN_LOG_ID="62291", STEP_NAME="ST0205_2", ORA-06
575: Package or function PRC0205_2 is in an invali
d state
---t0205_34也驗證了該條記錄是有ST0205_4插入的
col c1 format a20
col c2 format a20
col tm format a50
set linesize 100
SQL> select * from t0205_2;
no rows selected
SQL>
SQL> select * from t0205_34;
C1 C2 TM
-------------------- -------------------- --------------------------------------------------
J0205_7 ST0205_4 08-FEB-15 09.54.04.447778 AM
alter table t0205_2
exec dbms_scheduler.set_attribute('J0205_7','start_date',systimestamp+1/1440);
exec dbms_scheduler.set_attribute('J0205_7','end_date',systimestamp+10/1440);
exec dbms_Scheduler.stop_job('J0205_7');
exec dbms_scheduler.drop_job('J0205_7');
exec dbms_scheduler.drop_job('J0205_0');
exec dbms_scheduler.drop_chain('cha0205_1');
exec dbms_scheduler.drop_program('P0205_2,P0205_34');
exec dbms_scheduler.drop_schedule('est0205_1');
truncate table t0205_2;
truncate table t0205_34;
///////////////////////////
// 11、建立一個event-based job
///////////////////////////
兩個job：J0205_3、J0205_4，其中J0205_3啟動後及完成後均會自動觸發J0205_4執行，J0205_3啟動和完成階段產生的事件訊息會進入名為SYS.SCHEDULER$_EVENT_QUEUE的佇列，該佇列專門用於存放scheduler執行期間產生的event message，J0205_4的owner必須有許可權從這個佇列中把和自己有關的event message Dequeue出來如果判斷是J0205_3發來的則觸發J0205_4開始執行
---建立測試表
create table t0205_3 (c1 varchar2(10),tm timestamp,event_info varchar2(500));
---建立J0205_3執行所需的procedure、program
create or replace procedure PRC0205_3(v_jname in varchar2,v_jstart in timestamp)
as
begin
insert into t0205_3(c1,tm,event_info) values(v_jname,v_jstart,v_jname||' start');
dbms_lock.sleep(30);
insert into t0205_3(c1,tm,event_info) values(v_jname,systimestamp,v_jname||' end');
commit;
end;
/
exec dbms_scheduler.create_program(program_name=>'P0205_3',program_type=>'STORED_PROCEDURE',program_action=>'PRC0205_3',number_of_arguments=>2);
--定義program引數
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_3',metadata_attribute=>'job_name',argument_position=>1);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_3',metadata_attribute=>'job_start',argument_position=>2);
--建立J0205_3，並指定其開始及成功結束後均生成event
exec dbms_scheduler.create_job(job_name=>'J0205_3',program_name=>'P0205_3',start_date=>systimestamp+1/1440,repeat_interval=>'FREQ=MINUTELY',end_date=>systimestamp+10/1440,auto_drop=>FALSE);
exec dbms_scheduler.set_attribute('J0205_3','raise_events',DBMS_SCHEDULER.JOB_STARTED+DBMS_SCHEDULER.JOB_SUCCEEDED);
--賦予ad使用者訪問SYS.SCHEDULER$_EVENT_QUEUE佇列的許可權，建立agent，agent名稱為ad_agent(兩種方法二選一)
***SYS使用者執行
exec DBMS_AQADM.ENABLE_DB_ACCESS('ad_agent','ad');
***也可以使用scott使用者執行
exec DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('ad_agent');
--建立基於event的schedule，這個schedule的觸發條件是：事件佇列SYS.SCHEDULER$_EVENT_QUEUE里名為ad_agent收到J0205_3傳送來的Event message
exec dbms_scheduler.create_event_schedule('SC0205_3',event_condition=>'tab.user_data.object_name=''J0205_3''',queue_spec=>'SYS.SCHEDULER$_EVENT_QUEUE,ad_agent');
--建立J0205_4執行所需的procedure、program，將SYS.SCHEDULER$_EVENT_QUEUE的資訊輸出至v_message變數並存入到t0205_3表的event_info欄位
create or replace procedure PRC0205_4(v_jname in varchar2,v_jstart in timestamp,v_event_info in SYS.SCHEDULER$_EVENT_INFO)
as
v_message varchar2(1024);
begin
v_message:='EVENT_TYPE:'||v_event_info.event_type||';;'||'OBJECT_NAME:'||v_event_info.object_name||';;'||'EVENT_TIMESTAMP:'||to_char(v_event_info.EVENT_TIMESTAMP,'yyyymmdd hh24:mi:ss.ff')||';;'||'EVENT_STATUS:'||v_event_info.EVENT_STATUS||';;'||'ERROR_CODE:'||v_event_info.error_code;
insert into t0205_3(c1,tm,event_info) values(v_jname,v_jstart,v_message);
commit;
end;
/
exec dbms_scheduler.create_program(program_name=>'P0205_4',program_type=>'STORED_PROCEDURE',program_action=>'PRC0205_4',number_of_arguments=>3);
--定義program引數
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_4',metadata_attribute=>'job_name',argument_position=>1);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_4',metadata_attribute=>'job_start',argument_position=>2);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_4',metadata_attribute=>'event_message',argument_position=>3);
--建立J0205_4，並Enable所有program和job
exec dbms_scheduler.create_job(job_name=>'J0205_4',program_name=>'P0205_4',schedule_name=>'SC0205_3',auto_drop=>FALSE);
exec dbms_scheduler.enable('P0205_3,P0205_4,J0205_3,J0205_4');
--檢查t0205_3表，J0205_4所在行的event_info列顯示了當時觸發J0205_4執行的事件訊息內容
SQL> select * from t0205_3 order by tm;
C1 TM EVENT_INFO
---------- ----------------------------------- ------------------------------------------------------------------------------------------------------------------------
J0205_3 05-FEB-15 03.42.08.080748 PM J0205_3 start
J0205_4 05-FEB-15 03.42.09.007749 PM EVENT_TYPE:JOB_STARTED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:42:08.080655000;;EVENT_STATUS:1;;ERROR_CODE:0
J0205_3 05-FEB-15 03.42.38.121844 PM J0205_3 end
J0205_4 05-FEB-15 03.42.39.005193 PM EVENT_TYPE:JOB_SUCCEEDED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:42:38.122522000;;EVENT_STATUS:0;;ERROR_CODE:0
J0205_3 05-FEB-15 03.43.08.105371 PM J0205_3 start
J0205_4 05-FEB-15 03.43.09.007843 PM EVENT_TYPE:JOB_STARTED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:43:08.105292000;;EVENT_STATUS:1;;ERROR_CODE:0
J0205_3 05-FEB-15 03.43.38.112703 PM J0205_3 end
J0205_4 05-FEB-15 03.43.39.003996 PM EVENT_TYPE:JOB_SUCCEEDED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:43:38.113498000;;EVENT_STATUS:0;;ERROR_CODE:0
附，上面用到的事件佇列訊息型別SYS.SCHEDULER$_EVENT_INFO結構如下
SQL> desc SYS.SCHEDULER$_EVENT_INFO
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT_TYPE VARCHAR2(4000)
OBJECT_OWNER VARCHAR2(4000)
OBJECT_NAME VARCHAR2(4000)
EVENT_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
ERROR_CODE NUMBER
ERROR_MSG VARCHAR2(4000)
EVENT_STATUS NUMBER
LOG_ID NUMBER
RUN_COUNT NUMBER
FAILURE_COUNT NUMBER
RETRY_COUNT NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 VARCHAR2(4000)
SPARE4 VARCHAR2(4000)
SPARE5 TIMESTAMP(6) WITH TIME ZONE
SPARE6 TIMESTAMP(6) WITH TIME ZONE
SPARE7 RAW(2000)
SPARE8 RAW(2000)
OBJECT_SUBNAME VARCHAR2(4000)
JOB_CLASS_NAME VARCHAR2(4000)
///////////////////////////
// 12、建立一個File arrival event based job
///////////////////////////
當/home/oracle目錄下存入新的以fwatch開頭的檔案，且檔案的size > 1位元組時，觸發名為J0208_3的job執行，J0208_3的功能就是將接收到的檔案資訊進行輸出
--賦予ad使用者修改FILE_WATCHER_SCHEDULE的許可權
sqlplus '/as sysdba'
grant alter on SYS.FILE_WATCHER_SCHEDULE to ad;
--建立測試表
drop table t0208_3;
create table t0208_3 (msg varchar2(2000),tm timestamp);
--建立procedure，用於輸出訊息佇列裡存放的檔案資訊
create or replace procedure PRC0208_3 (v_file_result in SYS.SCHEDULER_FILEWATCHER_RESULT)
as
v_out_msg varchar2(2000);
begin
v_out_msg:='destination:'||v_file_result.destination||'---'||'directory_path:'||v_file_result.directory_path||'/'||v_file_result.actual_file_name||'---'||'file_size:'||v_file_result.file_size||'---'||'found_time:'||to_char(v_file_result.file_timestamp,'yyyymmdd hh24:mi:ss.ff TZH:TZM');
insert into t0208_3 values(v_out_msg,systimestamp);
commit;
end;
/
--建立credential、file_watcher、program
exec dbms_scheduler.create_credential(credential_name=>'C0208_3',username=>'oracle',password=>'uiop7890');
exec dbms_scheduler.create_file_Watcher(file_watcher_name=>'FW0208_3',directory_path=>'/home/oracle',file_name=>'fwatch*',credential_name=>'c0208_3',enabled=>FALSE);
exec dbms_scheduler.create_program(program_name=>'P0208_3',program_type=>'STORED_PROCEDURE',program_action=>'PRC0208_3',number_of_arguments=>1);
exec dbms_scheduler.define_metadata_argument(program_name=>'P0208_3',metadata_attribute=>'event_message',argument_position=>1);
--建立job,event_condition=>'tab.user_data.file_size > 1表示只有size>1位元組的檔案才會觸發job執行
exec dbms_scheduler.create_job(job_name=>'J0208_3',program_name=>'P0208_3',event_condition=>'tab.user_data.file_size > 1',queue_spec=>'FW0208_3',end_Date=>systimestamp+300/1440,auto_Drop=>FALSE);
--修改FILE_WATCHER_SCHEDULE監測的時間為每分鐘一次
exec dbms_scheduler.set_attribute('SYS.FILE_WATCHER_SCHEDULE','repeat_interval','FREQ=MINUTELY');
--enable all objects
exec dbms_Scheduler.enable('P0208_3,FW0208_3,J0208_3');
--手工在/home/oracle/下建立檔案fwatch1.txt
oracle@jq570322b:/home/oracle>ls -l fwatch1.txt
-rwxr-xr-x 1 oracle oinstall 1438 Feb 08 17:55 fwatch1.txt
--過1分鐘後，檢視佇列表sys.aq$scheduler_filewatcher_qt，可以看出對於fwatch1.txt檔案，對應兩條記錄，一條狀態為READY是進入佇列的記錄，狀態為PROCESSED的是出佇列記錄
set pause on
set linesize 180
col enq_time format a9
col deq_time format a9
col msg_state format a10
col user_data format a90
col consumer_name format a10
select enq_time,deq_time,msg_state,consumer_name,user_data from sys.aq$scheduler_filewatcher_qt order by enq_time desc;
ENQ_TIME DEQ_TIME MSG_STATE CONSUMER_N USER_DATA(DESTINATION, DIRECTORY_PATH, ACTUAL_FILE_NAME, FILE_SIZE, FILE_TIMESTAMP, TS_MS_
--------- --------- ---------- ---------- ------------------------------------------------------------------------------------------
20150208 20150208 PROCESSED SCHEDULER$ SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15
17:57:01 17:57:01 _EVENT_AGE 09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH
NT ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))
20150208 READY SCHED$_AGT SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15
17:57:01 $_53 09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH
ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))
--sys.SCHEDULER_FILEWATCHER_RESULT object裡的file_timestamp是以GMT時間顯示的，所以msg裡看到的時間會相差8小時，其表示的是檔案建立的時間
col msg format a90
col tm format a30
set linesize 150
select * from t0208_3;
MSG TM
------------------------------------------------------------------------------------------ ------------------------------
destination:jq570322b---directory_path:/home/oracle/fwatch1.txt---file_size:1438---found_time 08-FEB-15 05.57.02.028360 PM
:20150208 09:55:53.000000000 +00:00
--手工在/home/oracle/下建立檔案fwatch2.txt，大小為1位元組
oracle@jq570322b:/home/oracle>ls -l fwatch2.txt
-rw-r--r-- 1 oracle oinstall 1 Feb 08 17:57 fwatch2.txt
--過了1分鐘後sys.aq$scheduler_filewatcher_qt對應fwatch2.txt多了一條Ready的記錄，但並沒有PROCESSED的記錄，這是因為fwatch2.txt的file_size不滿足>1的條件所以不予以處理
select enq_time,deq_time,msg_state,consumer_name,user_data from sys.aq$scheduler_filewatcher_qt order by enq_time desc
ENQ_TIME DEQ_TIME MSG_STATE CONSUMER_N USER_DATA(DESTINATION, DIRECTORY_PATH, ACTUAL_FILE_NAME, FILE_SIZE, FILE_TIMESTAMP, TS_MS_
--------- --------- ---------- ---------- ------------------------------------------------------------------------------------------
20150208 READY SCHED$_AGT SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch2.txt', 1, '08-FEB-15 09.
17:59:31 $_53 57.39.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCHER_
REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))
20150208 20150208 PROCESSED SCHEDULER$ SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15
17:57:01 17:57:01 _EVENT_AGE 09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH
NT ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))
20150208 READY SCHED$_AGT SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15
17:57:01 $_53 09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH
ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))
--t0208_3表裡的記錄沒有變化
col msg format a90
col tm format a30
set linesize 150
select * from t0208_3;
MSG TM
------------------------------------------------------------------------------------------ ------------------------------
destination:jq570322b---directory_path:/home/oracle/fwatch1.txt---file_size:1438---found_time 08-FEB-15 05.57.02.028360 PM
:20150208 09:55:53.000000000 +00:00
TYPE scheduler_filewatcher_result IS OBJECT (
destination VARCHAR2(4000),
directory_path VARCHAR2(4000),
actual_file_name VARCHAR2(4000),
file_size NUMBER,
file_timestamp TIMESTAMP WITH TIME ZONE,
ts_ms_from_epoch NUMBER,
matching_requests SYS.SCHEDULER_FILEWATCHER_REQ_LIST);
#exec dbms_scheduler.set_attribute('S0208_3','event_spec','tab.user_data.file_size>1','FW0208_3');
#
#exec dbms_scheduler.stop_job('J0208_3');
#exec dbms_scheduler.drop_job('J0208_3');
#exec dbms_scheduler.drop_file_watcher('FW0208_3');
#exec dbms_scheduler.drop_program('P0208_3');
#exec dbms_scheduler.drop_schedule('S0208_3');
#
#exec dbms_scheduler.purge_log(job_name=>'J0208_3');
#exec dbms_scheduler.drop_file_watcher('FW0208_3');
///////////////
// 13、建立一個chain型別的job，使用evaluation_interval引數
///////////////
evaluation_interval引數的實用之處在於能夠定時去判定chain定義之外的條件是否滿足，如果滿足則開始執行特定的step，例如下面的例子完成的任務是：當ST0210_6步驟執行成功且trigtab表不為空，則開始執行ST0210_5這個步驟
drop table t0210_5;
drop table trigtab;
exec dbms_scheduler.stop_job('J0210_5');
exec dbms_scheduler.drop_job('J0210_5');
exec dbms_Scheduler.drop_chain('cha0210_5');
exec dbms_scheduler.drop_program('P0210_5,P0210_6');
--create table and procedure for test
create table t0210_5(c1 varchar2(10),tm timestamp);
create table trigtab(id number);
create or replace procedure PRC0210_5
as
begin
insert into t0210_5 values('detected',systimestamp);
commit;
end;
/
create or replace procedure PRC0210_6
as
begin
dbms_output.put_line('AAA');
end;
/
--create program、chain、step
exec dbms_scheduler.create_chain(chain_name=>'cha0210_5',rule_set_name=>NULL,evaluation_interval=>interval '1' minute);
exec dbms_scheduler.create_program(program_name=>'P0210_5',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_5');
exec dbms_scheduler.create_program(program_name=>'P0210_6',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_6');
exec dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_5',program_name=>'P0210_5');
exec dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_6',program_name=>'P0210_6');
--define chain rule R0210_61 to start the chain from step ST0210_6
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>'TRUE',action=>'start ST0210_6',rule_name=>'R0210_61');
--define chain rule R0210_62 to start ST0210_5 if ST0210_6 is succeeded and rowcount in trigtab is greater than 0 and Error occurs here：
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>':ST0210_6.state=''SUCCEEDED'' and (select count(*) from trigtab) > 0',action=>'start ST0210_5',rule_name=>'R0210_62');
exec dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>':ST0210_5.state=''SUCCEEDED''',action=>'END',rule_name=>'R0210_5');
--create a job and enable all scheduler objects
exec dbms_scheduler.create_job(job_name=>'J0210_5',job_type=>'CHAIN',job_action=>'cha0210_5',end_date=>sysdate+100/1440,repeat_interval=>'FREQ=MINUTELY;INTERVAL=5');
exec dbms_scheduler.enable('cha0210_5,P0210_5,P0210_6,J0210_5');
---驗證結果
select * from t0210_5;
no rows selected
select * from trigtab;
no rows selected
insert into trigtab values(1);
commit;
--過了1分鐘後，立馬有資料進來，就是因為設定了evaluation_interval之後，才能這麼及時
select * from t0210_5;
C1 TM
---------- ------------------------------
detected 12-FEB-15 09.24.45.116423 AM
truncate Table trigtab;
--如果將evaluation_interval設定為NULL，意味著不會定時去檢測這些外部條件，且即使到了job的發起時間也不會檢測，我們來看一下
exec dbms_scheduler.stop_job('J0210_5');
exec dbms_scheduler.set_attribute_null('cha0210_5','evaluation_interval');
---修改完屬性後J0210_5於09.51.45開始執行
select * from dba_scheduler_job_log where job_name='J0210_5' order by log_date desc;
LOG_ID LOG_DATE OWNER JOB_NAME JOB_SUBNAME JOB_CLASS OPERATION STATUS
71422 12-FEB-15 09.51.45.108690 AM +08:00 AD J0210_5 ST0210_6 DEFAULT_JOB_CLASS RUN SUCCEEDED
71421 12-FEB-15 09.51.45.012109 AM +08:00 AD J0210_5 DEFAULT_JOB_CLASS CHAIN_START RUNNING
71417 12-FEB-15 09.50.23.868297 AM +08:00 AD J0210_5 CHAIN_RUN STOPPED
---由於trigtab裡沒有資料所以t0210_5的資料沒有更新
SQL> select count(*) from trigtab;
COUNT(*)
----------
0
SQL> select * from t0210_5;
C1 TM
---------- ----------------------------------------
detected 12-FEB-15 09.24.45.116423 AM
detected 12-FEB-15 09.26.45.208953 AM
detected 12-FEB-15 09.37.45.111696 AM
SQL> insert into trigtab values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-----------------
20150212 09:52:17
---因為是每隔5分鐘執行一次，J0210_5下一次的執行時間將會是12-FEB-15 09.56.45，但到了20150212 09:57仍然沒有資料進入到t0210_5表
SQL> select sysdate from dual;
SYSDATE
-----------------
20150212 09:57:40
select * from t0210_5;
C1 TM
---------- ----------------------------------------
detected 12-FEB-15 09.24.45.116423 AM
detected 12-FEB-15 09.26.45.208953 AM
detected 12-FEB-15 09.37.45.111696 AM
--原因是對於step以外的步驟作為condition的，這裡指的是(select count(*) from trigtab) > 0'，必須要使用evaluation_interval，這是admin guide裡的一段原話useful to start chain steps based on time of day or based on occurrences external to the chain，重新把evaluation_interval設上
exec dbms_scheduler.set_attribute('cha0210_5','evaluation_interval',interval '1' minute);
exec dbms_scheduler.stop_job('J0210_5');
SQL> insert into trigtab values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate from dual
2 ;
SYSDATE
-----------------
20150212 10:22:12
SQL> select last_start_date from dba_scheduler_jobs where job_name='J0210_5';
LAST_START_DATE
---------------------------------------------------------------------------
12-FEB-15 10.21.45.010595 AM +08:00
---由於J0210_5上一次的執行時間在10.21.45，預計新插入記錄的時間戳為10.22.45，查詢結果驗證了我們的判斷
SQL> select * from t0210_5;
C1 TM
---------- ----------------------------------------
detected 12-FEB-15 10.22.45.111776 AM
detected 12-FEB-15 09.24.45.116423 AM
detected 12-FEB-15 09.26.45.208953 AM
detected 12-FEB-15 09.37.45.111696 AM
///////////////////////////////////////////////////////////////////////////////////////////////
/// 14、建立remote external job
///////////////////////////////////////////////////////////////////////////////////////////////
前提是要在執行remote external job的remote主機上安裝好schedule agent並完成註冊，具體可參照。。。。。？？？？,jq570321a為remote host，jq570322b為job所在的資料庫伺服器
---在remote host JQ570321A上建立Shell指令碼
vi /home/sagent/oracle1.sh;chmod u+x /home/sagent/oracle1.sh
#!/usr/bin/ksh
export TZ=BEIST-8
date > /home/sagent/oracle1.log
指令碼里必須包含的內容是shell直譯器、必要的使用者環境變數，比如上述內容中如果沒有export TZ=BEIST-8這行，那麼輸出結果中的時間將是UTC時間
--在remote host JQ570321A的/etc/hosts裡新增即將job所在的資料庫伺服器ip地址和主機名對應關係，這一步很重要決定了job在remote host上執行完畢後能否將結果回傳到資料庫伺服器，如果不新增這個對應關係會發現這個job始終處於running狀態，在agent.log裡會出現Submitting results failed with error: java.net.UnknownHostException: jq570322b的錯誤
echo "10.10.141.209 jq570322b" >> /etc/hosts
---SYS使用者賦權
sqlplus '/as sysdba'
grant create external job to scott;
grant create job to scott;
---Scott使用者建立program、credential、job等物件，destination_name為Schedule agent向資料庫註冊時獲得的名稱，一般都是主機名，可從dba_SCHEDULER_EXTERNAL_DESTS.destination_name獲得
sqlplus scott/773946
exec DBMS_SCHEDULER.drop_job(job_name=>'J0215_8',force=>TRUE);
exec DBMS_SCHEDULER.purge_log(job_name=>'J0215_8');
exec DBMS_SCHEDULER.drop_CREDENTIAL(credential_name=>'C0215_8');
exec DBMS_SCHEDULER.drop_program(program_name=>'P0215_8');
exec DBMS_SCHEDULER.create_program(program_name=>'P0215_8',program_type=>'EXECUTABLE',program_action=>'/home/sagent/oracle1.sh');
exec DBMS_SCHEDULER.CREATE_CREDENTIAL(credential_name=>'C0215_8',username=>'sagent',password=>'asdf3_14');
exec DBMS_SCHEDULER.create_job(job_name=>'J0215_8',program_name=>'P0215_8',start_Date=>sysdate+0.3/1440,end_date=>sysdate+60/1440,repeat_interval=>'FREQ=SECONDLY;INTERVAL=30',credential_name=>'C0215_8',destination_name=>'SYS.JQ570321A',auto_Drop=>FALSE); <=這裡destination_name別忘了前面加上sys.
exec DBMS_SCHEDULER.enable('P0215_8');
exec DBMS_SCHEDULER.enable('P0215_8,J0215_8');
---登陸remote host檢查執行結果
sagent@jq570321a:/home/sagent>cat oracle1.log
BEIST-8
Mon Feb 16 07:14:46 BEIST 2015
sagent@jq570321a:/home/sagent>ls -rlt oracle1.log
-rw-r----- 1 sagent dba 39 Feb 16 07:14 oracle1.log
---每次執行後會在remote主機的agent.log下留下排程記錄，agent.log中日誌記錄的詳細程度可以透過schagent.conf裡的LOGGING_LEVEL進行指定
cd $ORACLE_HOME/scheduler/execution_agent/data
tail -n 2 agent.log
2015.02.16 07:14:46 GMT+08:00 Log ID: job_32832_128 Name: SCOTT.J0215_8 Source: jq570322b : 30415 Source DB: TSTDB1 Command: /home/sagent/oracle1.sh
2015.02.16 07:14:46 GMT+08:00 Job terminated successfully. Duration: 0.02 seconds
/////////////////////
// 15、修改job class所關聯的consumer group以實現對該job_class下所有job進行優先順序調整的目的
/////////////////////
假設名為jclass1的job class裡的所有job在白天執行優先順序較高，到了晚上執行優先順序較低，那麼在白天可以將job class關聯到擁有較多資源的資源組cgday，而到了晚上將job class關聯到擁有較少資源的資源組cgnight
--建立資源計劃resplan1等物件，以sysdba使用者執行
exec dbms_resource_manager.create_pending_area;
exec dbms_resource_manager.create_plan(plan=>'resplan1',comment=>'resplan1');
exec dbms_resource_manager.create_consumer_group(consumer_group=>'cgday',comment=>'cgday');
exec dbms_resource_manager.create_consumer_group(consumer_group=>'cgnight',comment=>'cgnight');
exec dbms_resource_manager.create_plan_directive(plan=>'resplan1',group_or_subplan=>'cgday',mgmt_p1=>50,comment=>'dir_cgday');
exec dbms_resource_manager.create_plan_directive(plan=>'resplan1',group_or_subplan=>'cgnight',mgmt_p1=>20,comment=>'dir_cgnight');
exec dbms_resource_manager.create_plan_directive(plan=>'resplan1',group_or_subplan=>'OTHER_GROUPS',mgmt_p1=>30,comment=>'dir_other');
exec dbms_resource_manager.submit_pending_area;
exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(grantee_name=>'AD',consumer_group=>'cgday',grant_option=>FALSE);
exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(grantee_name=>'AD',consumer_group=>'cgnight',grant_option=>FALSE);
--賦給普通使用者ad必要的許可權，後面的操作若不加以說明則均以ad使用者執行
grant manage scheduler to ad;
grant create job to ad;
--清理scheduler物件
sqlplus ad/Uiop246!
drop table tclass1;
select 'exec dbms_scheduler.close_window('''||window_name||''');',active from dba_scheduler_windows where active='TRUE'; --關閉所有open的window
exec dbms_scheduler.drop_job(job_name=>'J131_4');
exec dbms_scheduler.drop_program_argument(program_name=>'p131_4',argument_position=>1);
exec dbms_scheduler.drop_program(program_name=>'p131_4');
exec dbms_scheduler.drop_job_class(job_class_name=>'jclass1');
exec dbms_scheduler.drop_window(window_name=>'w131_4');
exec dbms_scheduler.drop_schedule(schedule_name=>'S131_4');
--建立job_class，先關聯cgday這個resource group，注意job_class和window永遠是建立在sys使用者下的
exec dbms_scheduler.create_job_class(job_class_name=>'jclass1',resource_consumer_group=>'cgday');
col owner format a15
col object_name format a15
col object_type format a15
set linesize 100
select owner,object_name,object_type from dba_objects where object_name='JCLASS1';
OWNER OBJECT_NAME OBJECT_TYPE
--------------- --------------- ---------------
SYS JCLASS1 JOB CLASS
--以sys使用者執行：賦權job class的執行許可權給ad，否則後面在enable job或者run job的時候會收到ORA-27486: insufficient privileges
grant execute on jclass1 to ad;
--建立測試用表
create table tclass1 (jname varchar2(20),sid_serial varchar2(30),resource_cgname varchar2(10),tm timestamp);
--建立儲存過程
create or replace procedure query_jobinfo(input_jobname in varchar2) as
v_sid_serial varchar2(30);
v_resource_cgname varchar2(10);
v_tm timestamp;
begin
select ''''||sid||'-'||serial#||'''',RESOURCE_CONSUMER_GROUP into v_sid_serial,v_resource_cgname from v$session where sid=sys_context('userenv','sid');
insert into tclass1 values(input_jobname,v_sid_serial,v_resource_cgname,systimestamp);
commit;
end;
/
--建立program
exec dbms_scheduler.create_program(program_name=>'p131_4',program_type=>'STORED_PROCEDURE',program_action=>'query_jobinfo',number_of_arguments=>1);
--定義傳給program的引數
exec dbms_scheduler.define_metadata_argument(program_name=>'p131_4',metadata_attribute=>'job_name',argument_position=>1);
--建立schedule，從當前時間+1分鐘開始，每分鐘執行一次
exec dbms_scheduler.create_schedule(schedule_name=>'s131_4',start_date=>systimestamp+1/1440,repeat_interval=>'FREQ=MINUTELY',end_date=>systimestamp+100/1440);
--建立window，每個視窗持續時長為2分鐘
exec dbms_scheduler.create_window(window_name=>'w131_4',resource_plan=>'resplan1',schedule_name=>'s131_4',duration=>interval '2' minute);
--建立job，預設情況下job、program建立出來都是disabled狀態，job的log-level=OFF，但job_class的log-level是RUNS，這裡以job_class的日誌級別為準
exec dbms_scheduler.create_job(job_name=>'j131_4',program_name=>'p131_4',job_class=>'jclass1',schedule_name=>'sys.w131_4',auto_drop=>FALSE);
col job_name format a10
col job_Action format a10
col start_date format a15
col last_start_date format a15
col next_run_date format a15
col repeat_interval format a17
col end_Date format a15
col schedule_name format a20
set linesize 170
set pagesize 200
select job_name,job_Action,schedule_name,auto_drop,start_date,repeat_interval,end_Date,LAST_START_DATE,next_run_date,enabled,state,logging_level from dba_Scheduler_jobs where job_name in ('J131_4');
JOB_NAME JOB_ACTION SCHEDULE_NAME AUTO_ START_DATE REPEAT_INTERVAL END_DATE LAST_START_DATE NEXT_RUN_DATE ENABL STATE LOGGING_LEV
---------- ---------- -------------------- ----- --------------- ----------------- --------------- --------------- --------------- ----- --------------- -----------
J131_4 W131_4 FALSE FALSE DISABLED OFF
SQL> select logging_level from dba_scheduler_job_classes where job_class_name='JCLASS1';
LOGGING_LEV
-----------
RUNS
col program_action format a20
SQL> select owner,program_name,program_action,enabled from dba_scheduler_programs where program_name='P131_4'
OWNER PROGRAM_NAME PROGRAM_ACTION ENABL
--------------- ------------------------------ -------------------- -----
AD P131_4 query_jobinfo FALSE
--enable job、program
exec dbms_scheduler.enable('p131_4,j131_4');
--執行結果顯示J131_4這個job執行期間確實歸屬於cgday這個資源組
col tm format a40
set linesize 150
select * from tclass1;
JNAME SID_SERIAL RESOURCE_C TM
-------------------- ------------------------------ ---------- ---------------------------------------------------------------------------
J131_4 '6151-29923' CGDAY 01-FEB-15 01.31.20.180852 AM
J131_4 '6624-31309' CGDAY 01-FEB-15 01.26.16.292778 AM
J131_4 '6624-31317' CGDAY 01-FEB-15 01.28.20.182073 AM
--dba_scheduler_jobs的enable屬性從enable=false變為了enable=true
select job_name,job_Action,schedule_name,auto_drop,start_date,repeat_interval,end_Date,LAST_START_DATE,next_run_date,enabled,state,logging_level from dba_Scheduler_jobs where job_name in ('J131_4');
JOB_NAME JOB_ACTION SCHEDULE_NAME AUTO_ START_DATE REPEAT_INTERVAL END_DATE LAST_START_DATE NEXT_RUN_DATE ENABL STATE LOGGING_LEV
---------- ---------- -------------------- ----- --------------- ----------------- --------------- --------------- --------------- ----- --------------- -----------
J131_4 W131_4 FALSE 01-FEB-15 01.31 TRUE SCHEDULED OFF
.20.164953 AM +
08:00
--job執行期間dba_scheduler_job_log、dba_Scheduler_job_run_details、dba_Scheduler_window_log同步輸出的內容如下
col job_name format a10
col operation format a7
col status format a10
col log_date format a50
col additional_info format a50
set linesize 170
select job_name,operation,status,log_date,additional_info from dba_scheduler_job_log where job_name in ('J131_4') order by log_date desc;
JOB_NAME OPERATI STATUS LOG_DATE ADDITIONAL_INFO
---------- ------- ---------- -------------------------------------------------- --------------------------------------------------
J131_4 RUN SUCCEEDED 01-FEB-15 01.31.20.181922 AM +08:00
J131_4 RUN SUCCEEDED 01-FEB-15 01.28.20.182923 AM +08:00
J131_4 RUN SUCCEEDED 01-FEB-15 01.26.16.294369 AM +08:00
col job_name format a10
col log_Date format a30
col req_start_date format a30
col actual_start_date format a30
col run_duration format a20
col additional_info format a30;
set linesize 170
select job_name,log_date,status,req_start_date,actual_start_date,run_duration,additional_info from dba_Scheduler_job_run_details where job_name in ('J131_4') order by log_date desc;
JOB_NAME LOG_DATE STATUS REQ_START_DATE ACTUAL_START_DATE RUN_DURATION
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
ADDITIONAL_INFO
------------------------------
J131_4 01-FEB-15 01.31.20.182513 AM + SUCCEEDED 01-FEB-15 01.31.18.000000 AM + 01-FEB-15 01.31.20.165037 AM + +000 00:00:00
08:00 08:00 08:00
J131_4 01-FEB-15 01.28.20.183339 AM + SUCCEEDED 01-FEB-15 01.28.18.000000 AM + 01-FEB-15 01.28.20.166962 AM + +000 00:00:00
08:00 08:00 08:00
J131_4 01-FEB-15 01.26.16.294804 AM + SUCCEEDED 01-FEB-15 01.25.18.000000 AM + 01-FEB-15 01.26.16.270091 AM + +000 00:00:00
08:00 08:00 08:00
col log_date format a50
col window_name format a15
col operation format a10
set linesize 150
select log_date,window_name,operation from dba_Scheduler_window_log where window_name='W131_4' order by log_date desc;
LOG_DATE WINDOW_NAME OPERATION
-------------------------------------------------- --------------- ----------
01-FEB-15 01.31.18.074558 AM +08:00 W131_4 OPEN
01-FEB-15 01.30.18.012337 AM +08:00 W131_4 CLOSE
01-FEB-15 01.28.18.074588 AM +08:00 W131_4 OPEN
01-FEB-15 01.27.18.012556 AM +08:00 W131_4 CLOSE
01-FEB-15 01.25.18.073275 AM +08:00 W131_4 OPEN
按照我們對於schedule及window屬性的設定值，視窗W131_4每隔1分鐘開啟一次，每次開啟時長為2分鐘，對於從dba_Scheduler_window_log反映出的資訊可知視窗W131_4每次開啟的時間點距離上一次開啟的時間點相隔3分鐘，視窗W131_4首次開啟的時間是01-FEB-15 01.25.18.073275，而從dba_scheduler_job_log看出job J131_4首次執行的時間是01-FEB-15 01.26.16.294804，這個時間差是由於job J131_4建立完之後處於disable狀態，直到01-FEB-15 01.26.16.294804這個時間點才被enable，所以造成了dba_Scheduler_job_run_details裡log_date=01-FEB-15 01.26.16.294804的那條記錄REQ_START_DATE和ACTUAL_START_DATE欄位有大約1分鐘的時間差，在這個例子裡，job實際的執行間隔時間是3分鐘，其中2分鐘來自於視窗duration=>interval '2' minute的定義，1分鐘來自於Schedule repeat_interval=>'FREQ=MINUTELY'的定義。
--假設現在到了晚上，我們把jclass1這個job class關聯到cgnight資源組
col tm format a40
set linesize 150
set pagesize 200
SQL> select * from tclass1 order by tm desc;
JNAME SID_SERIAL RESOURCE_C TM
-------------------- ------------------------------ ---------- ----------------------------------------
J131_4 '1424-28025' CGDAY 01-FEB-15 04.30.34.198992 AM
J131_4 '1424-28011' CGDAY 01-FEB-15 04.27.34.192817 AM
J131_4 '1424-27997' CGDAY 01-FEB-15 04.24.34.016318 AM
J131_4 '1424-27983' CGDAY 01-FEB-15 04.21.33.550288 AM
.....省略了部分輸出
exec dbms_scheduler.set_attribute('SYS.jclass1','resource_consumer_group','cgnight');
SQL> SELECT JOB_CLASS_NAME,RESOURCE_CONSUMER_GROUP from dba_scheduler_job_classes where job_class_name='JCLASS1';
JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
JCLASS1 CGNIGHT
---稍等片刻發現接下來發起的job確實切換到了cgnight資源組
SQL> select * from tclass1 order by tm desc;
JNAME SID_SERIAL RESOURCE_C TM
-------------------- ------------------------------ ---------- ----------------------------------------
J131_4 '1424-28077' CGNIGHT 01-FEB-15 04.42.34.195001 AM
J131_4 '1424-28063' CGNIGHT 01-FEB-15 04.39.34.172776 AM
J131_4 '1424-28049' CGDAY 01-FEB-15 04.36.34.199302 AM
J131_4 '1424-28035' CGDAY 01-FEB-15 04.33.34.195134 AM
J131_4 '1424-28025' CGDAY 01-FEB-15 04.30.34.198992 AM
J131_4 '1424-28011' CGDAY 01-FEB-15 04.27.34.192817 AM
J131_4 '1424-27997' CGDAY 01-FEB-15 04.24.34.016318 AM
J131_4 '1424-27983' CGDAY 01-FEB-15 04.21.33.550288 AM
--dba_scheduler_windows檢視下一次視窗open的時間是01-FEB-15 04.51.32，Enabled=TRUE，如果本次已經是最後一次open了，那麼enabled顯示為FALSE
SSQL> select window_name,enabled,active,LAST_START_DATE,NEXT_START_DATE from dba_scheduler_windows where window_name='W131_4';
WINDOW_NAM ENABL ACTIV LAST_START_DATE NEXT_START_DATE
---------- ----- ----- ----------------------------------- -----------------------------------
W131_4 TRUE TRUE 01-FEB-15 04.51.32.002562 AM +08:00 01-FEB-15 04.51.32.000000 AM +08:00
/////////////////////
// 16、要使job正常執行，job的執行者必須對job裡指定的program有Execute許可權，還要對program所指向的儲存過程等物件有執行許可權
/////////////////////
這裡分為program_type=STORED_PROCEDURE和program_type=EXECUTABLE兩種場景
---------------1、先來看看program_type=STORED_PROCEDURE的情況----------
資料庫裡的兩個使用者scott2和scott3
grant resource,connect,create job,unlimited tablespace to scott2 identified by "773946" ;
grant resource,connect,create job,unlimited tablespace to scott3 identified by "773946" ;
--connect as scott2，建立一個procedure名為proc2，把執行許可權賦給scott3
create or replace procedure proc2
is
begin
dbms_output.put_line('scott2''s proc2');
end;
/
grant execute on proc2 to scott3;
--connect as scott2，建立一個program，把執行許可權賦給scott3
exec dbms_scheduler.create_program(program_name=>'scott2_p201',program_type=>'STORED_PROCEDURE',program_action=>'scott2.proc2');
exec dbms_scheduler.enable('scott2.scott2_p201');
grant execute on scott2_p201 to scott3;
--connect as scott3，建立一個job，實質是透過scott2.scott2_p201呼叫Scott2.proc2，因此scott3既要有執行program scott2.scott2_p201的許可權也要有執行procedure scott.proc2的許可權，兩者缺一不可
exec dbms_scheduler.drop_job(job_name=>'scott3_j201');
exec dbms_scheduler.create_job(job_name=>'scott3_j201',program_name=>'scott2.scott2_p201',start_date=>NULL,auto_drop=>FALSE,repeat_interval=>NULL,end_date=>NULL);
exec dbms_scheduler.enable('scott3_j201');
SQL> set serveroutput on
SQL> exec dbms_scheduler.run_job(job_name=>'scott3_j201');
scott2's proc2
PL/SQL procedure successfully completed.
--connect as scott2，如果取消Scott3對scott2.proc2儲存過程的執行許可權
revoke execute on proc2 from scott3;
--connect as scott3，執行job時報ORA-00942
SQL> exec dbms_scheduler.run_job(job_name=>'scott3_j201');
BEGIN dbms_scheduler.run_job(job_name=>'scott3_j201'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
--connect as scott2，如果取消Scott3對scott2.scott2_p201 program的執行許可權
revoke execute on scott2_p201 from scott3;
--connect as scott3，執行job時報ORA-27476
SQL> exec dbms_scheduler.run_job(job_name=>'scott3_j201');
BEGIN dbms_scheduler.run_job(job_name=>'scott3_j201'); END;
*
ERROR at line 1:
ORA-27476: "SCOTT2.SCOTT2_P201" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
如果我們要讓scott4使用者執行scott3使用者下的job scott3_j201的許可權，賦予alter job的許可權給scott4即可，無需把scott2.scott2_p201、scott2.proc2執行許可權賦給Scott4，因為當scott4執行Scott3.scott3_j201時就是以Scott3身份執行的
grant resource,connect,create job,unlimited tablespace to scott4 identified by "773946" ;
--connect as scott3
grant alter on scott3_j201 to scott4;
--connect as scott4
SQL> exec dbms_scheduler.run_job(job_name=>'scott3.scott3_j201');
scott2's proc2
PL/SQL procedure successfully completed.
---------------2、再看一下program_type=EXECUTABLE的情況----------
作業系統裡tstdb1使用者下有名為tstdb1.sh的指令碼，指令碼內容和執行許可權如下
tstdb1@jq570322b:/home/tstdb1>cat tstdb1.sh
cat tstdb1.sh
echo scripts for tstdb1 > /tmp/$LOGNAME.`date +%m%d%H%M`
sleep 5
tstdb1@jq570322b:/home/tstdb1>ls -l tstdb1.sh
-rwxr----- 1 tstdb1 oinstall 24 Feb 01 21:52 tstdb1.sh
--賦予scott使用者create external job許可權
sqlplus '/as sysdba'
grant create external job to scott;
--Scott使用者建立並執行指令碼，提示沒有許可權
sqlplus scott/773946
exec dbms_scheduler.create_program(program_name=>'scott_201_p1',program_type=>'EXECUTABLE',program_action=>'/home/tstdb1/tstdb1.sh');
exec dbms_Scheduler.create_job(job_name=>'scott_201_j1',program_name=>'scott_201_p1',start_date=>NULL,repeat_interval=>NULL,end_date=>NULL,auto_drop=>FALSE);
exec dbms_Scheduler.enable('scott_201_p1,scott_201_j1');
exec dbms_Scheduler.run_job(job_name=>'scott_201_j1');
BEGIN dbms_Scheduler.run_job(job_name=>'scott_201_j1'); END;
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
上面在執行dbms_Scheduler.run_job的同時，能在OS級別看到有nobody使用者發起的呼叫/home/tstdb1/tstdb1.sh指令碼的命令
tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1.sh | grep -v grep
nobody 36962418 1 0 22:26:44 - 0:00 extjob tstdb1 6357704 -exec /home/tstdb1/tstdb1.sh
上面的輸出中6357704對應的是oracle shadow process的作業系統程式號，如果run_job時使用了use_current_session=FALSE，那麼也能在dba_scheduler_job_run_details.slave_pid找到這個程式號，對於local external job，如果在建立job的時候不明確指定credential資訊，且這個job建立在除了SYS使用者之外的其它schema底下，會使用$ORACLE_HOME/rdbms/admin/externaljob.ora裡定義的使用者，這個使用者預設情況下就是nobody
--給指令碼賦上execute許可權後，執行job又報了ORA-27369
chmod o+x+r /home/tstdb1/tstdb1.sh
tstdb1@jq570322b:/home/tstdb1>ls -l tstdb1.sh
-rwxr--r-x 1 tstdb1 oinstall 80 Feb 02 09:00 tstdb1.sh
SQL> exec dbms_Scheduler.run_job(job_name=>'scott_201_j1',use_current_session=>TRUE);
BEGIN dbms_Scheduler.run_job(job_name=>'scott_201_j1',use_current_session=>TRUE); END;
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: 255
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
原因在於指令碼里沒有執行執行的shell，在tstdb1.sh指令碼第一行加入#!/usr/bin/ksh後執行成功
SQL> exec dbms_Scheduler.run_job(job_name=>'scott_201_j1',use_current_session=>TRUE);
PL/SQL procedure successfully completed.
tstdb1@jq570322b:/tmp>ls -l /tmp/nobody*
-rw-r----- 1 nobody nobody 19 Feb 02 09:10 /tmp/nobody.02020910
小提示：如果把job建在SYS使用者下，那麼預設會使用oracle software owner使用者(比如oracle)發起指令碼。
/////////////////////
// 17、同一個job class裡的兩個job，一個優先順序高，一個優先順序低，如果同時發起，是否低的會在高的之後發起
/////////////////////
sqlplus scott/773946
--建立測試表
create table t0202_1 (pri varchar2(10),tm timestamp);
--建立job class、job
exec DBMS_SCHEDULER.CREATE_JOB_CLASS(job_class_name=>'jc0202_1',logging_level=>DBMS_SCHEDULER.LOGGING_FULL);
exec dbms_scheduler.drop_job('j0202_1_high,j0202_1_low');
exec dbms_scheduler.create_job(job_name=>'j0202_1_high',job_class=>'jc0202_1',auto_drop=>FALSE,job_type=>'PLSQL_BLOCK',job_action=>'insert into t0202_1 values(''high_pri'',systimestamp);commit;',start_date=>to_timestamp_tz('20150202 15:20:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'),repeat_interval=>'FREQ=SECONDLY;interval=5',end_date=>to_timestamp_tz('20150202 15:22:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'));
exec dbms_scheduler.set_attribute('j0202_1_high','job_priority',1);
exec dbms_scheduler.create_job(job_name=>'j0202_1_low',job_class=>'jc0202_1',auto_drop=>FALSE,job_type=>'PLSQL_BLOCK',job_action=>'insert into t0202_1 values(''low_pri'',systimestamp);commit;',start_date=>to_timestamp_tz('20150202 15:20:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'),repeat_interval=>'FREQ=SECONDLY;interval=5',end_date=>to_timestamp_tz('20150202 15:22:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'));
exec dbms_scheduler.set_attribute('j0202_1_low','job_priority',5);
exec dbms_scheduler.enable('j0202_1_high,j0202_1_low');
--結果顯示一個job class裡的兩個job發起時間並不一定是priority高的在前
SQL> select LOG_ID,req_start_date,ACTUAL_START_DATE,JOB_NAME from dba_scheduler_job_run_details where job_name like 'J0202%' order by log_id desc;
LOG_ID REQ_START_DATE ACTUAL_START_DATE JOB_NAME
---------- ---------------------------------------- ---------------------------------------- --------------------
3436 02-FEB-15 03.21.55.000000 PM +08:00 02-FEB-15 03.21.55.006149 PM +08:00 J0202_1_LOW
3435 02-FEB-15 03.21.55.000000 PM +08:00 02-FEB-15 03.21.55.006115 PM +08:00 J0202_1_HIGH
3434 02-FEB-15 03.21.50.000000 PM +08:00 02-FEB-15 03.21.50.005914 PM +08:00 J0202_1_LOW
3433 02-FEB-15 03.21.50.000000 PM +08:00 02-FEB-15 03.21.50.005927 PM +08:00 J0202_1_HIGH
3432 02-FEB-15 03.21.45.000000 PM +08:00 02-FEB-15 03.21.45.009104 PM +08:00 J0202_1_HIGH
3431 02-FEB-15 03.21.45.000000 PM +08:00 02-FEB-15 03.21.45.009113 PM +08:00 J0202_1_LOW
3430 02-FEB-15 03.21.40.000000 PM +08:00 02-FEB-15 03.21.40.009268 PM +08:00 J0202_1_LOW
3429 02-FEB-15 03.21.40.000000 PM +08:00 02-FEB-15 03.21.40.009267 PM +08:00 J0202_1_HIGH
3428 02-FEB-15 03.21.35.000000 PM +08:00 02-FEB-15 03.21.35.009188 PM +08:00 J0202_1_HIGH
3427 02-FEB-15 03.21.35.000000 PM +08:00 02-FEB-15 03.21.35.009140 PM +08:00 J0202_1_LOW
3426 02-FEB-15 03.21.30.000000 PM +08:00 02-FEB-15 03.21.30.009066 PM +08:00 J0202_1_HIGH
SQL> select * from t0202_1 where tm>to_date('20150202 15:21:35','yyyymmdd hh24:mi:ss') order by tm desc;
PRI TM
---------- ---------------------------------------------------------------------------
low_pri 02-FEB-15 03.21.55.009320 PM
high_pri 02-FEB-15 03.21.55.009208 PM
low_pri 02-FEB-15 03.21.50.008531 PM
high_pri 02-FEB-15 03.21.50.008528 PM
high_pri 02-FEB-15 03.21.45.021677 PM
low_pri 02-FEB-15 03.21.45.011664 PM
low_pri 02-FEB-15 03.21.40.011878 PM
high_pri 02-FEB-15 03.21.40.011878 PM
high_pri 02-FEB-15 03.21.35.011766 PM
low_pri 02-FEB-15 03.21.35.011763 PM
/////////////////////////////////////////
// 18、測試database 異常當機重啟後能否繼續未完成的job
/////////////////////////////////////////
--build test table and procedure
sqlplus scott/773946
drop table t0204_1;
create table t0204_1 (c1 timestamp);
create or replace procedure prc0204_1 as
begin
while ( true ) loop
insert into t0204_1 values(systimestamp);
commit;
dbms_lock.sleep(5);
end loop;
end;
/
---建立job，restartable=TRUE一定要設定
exec dbms_scheduler.drop_job('J0204_1');
exec dbms_scheduler.drop_program('P0204_1');
exec dbms_scheduler.create_program(program_name=>'P0204_1',program_type=>'STORED_PROCEDURE',program_action=>'PRC0204_1');
exec dbms_scheduler.create_job(job_name=>'J0204_1',program_name=>'P0204_1',start_date=>systimestamp+0.5/1440,end_date=>systimestamp+10/1440,repeat_interval=>NULL,auto_drop=>FALSE);
exec dbms_scheduler.set_attribute('J0204_1','restartable',TRUE);
exec dbms_scheduler.enable('P0204_1,J0204_1');
select * from t0204_1;
SQL> select * from t0204_1;
C1
---------------------------------------------------------------------------
04-FEB-15 01.11.43.029742 PM
04-FEB-15 01.11.48.030364 PM
---restart database
shutdown abort
startup
---表裡繼續有資料insert進來，表明job自動恢復執行
SQL> select * from t0204_1;
C1
---------------------------------------------------------------------------
04-FEB-15 01.11.43.029742 PM
04-FEB-15 01.11.48.030364 PM
04-FEB-15 01.11.53.030663 PM
04-FEB-15 01.12.14.893963 PM --->斷點續跑
04-FEB-15 01.12.19.894506 PM
04-FEB-15 01.12.24.894893 PM
04-FEB-15 01.12.29.895404 PM
04-FEB-15 01.12.34.895867 PM
04-FEB-15 01.12.39.896308 PM
04-FEB-15 01.12.44.896671 PM
04-FEB-15 01.12.49.896998 PM
---J0204_1 job重啟時的日誌
col log_date format a40
col owner format a10
col job_name format a10
col operation format a10
col status format a10
col additional_info format a50
set linesize 170
set pagesize 120
select log_date,job_name,operation,status,additional_info from dba_scheduler_job_log where job_name='J0204_1' order by log_date desc;
LOG_DATE JOB_NAME OPERATION STATUS ADDITIONAL_INFO
---------------------------------------- ---------- ---------- ---------- --------------------------------------------------
04-FEB-15 01.12.14.550349 PM +08:00 J0204_1 RUN STOPPED REASON="ORA-01014: ORACLE shutdown in progress"
///////////////////////////////////////
///19、exec dbms_Scheduler.enable用於job時commit_semantics引數的測試
///////////////////////////////////////
###建立procedure、program、job等物件，其中J0209_2故意指向一個不存在的program
exec dbms_scheduler.drop_job('j0209_1,j0209_2,j0209_3');
create or replace procedure PRC0209_1
as
aaa number;
begin
select count(*) into aaa from all_users;
end;
/
exec dbms_scheduler.create_program(program_name=>'P0209_1',program_type=>'STORED_PROCEDURE',program_action=>'PRC0209_1');
exec dbms_Scheduler.enable(name=>'P0209_1');
set serveroutput on
exec dbms_scheduler.create_job(job_name=>'j0209_1',program_name=>'P0209_1',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);
exec dbms_scheduler.create_job(job_name=>'j0209_2',program_name=>'P0209_111',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);
exec dbms_scheduler.create_job(job_name=>'j0209_3',program_name=>'P0209_1',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);
###使用STOP_ON_FIRST_ERROR Enable，結果僅有J0209_1 Enable
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');
JOB_NAME ENABL
------------------------------ -----
J0209_3 FALSE
J0209_2 FALSE
J0209_1 FALSE
exec dbms_Scheduler.enable(name=>'j0209_1,j0209_2,j0209_3',commit_semantics=>'STOP_ON_FIRST_ERROR');
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');
JOB_NAME ENABL
------------------------------ -----
J0209_3 FALSE
J0209_2 FALSE
J0209_1 TRUE
###disable掉'j0209_1,j0209_2,j0209_3'三個job，使用ABSORB_ERRORS Enable，結果J0209_1、J0209_3 Enable
exec dbms_Scheduler.disable(name=>'j0209_1,j0209_2,j0209_3');
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');
JOB_NAME ENABL
------------------------------ -----
J0209_3 FALSE
J0209_2 FALSE
J0209_1 FALSE
exec dbms_Scheduler.enable(name=>'j0209_1,j0209_2,j0209_3',commit_semantics=>'ABSORB_ERRORS');
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');
JOB_NAME ENABL
------------------------------ -----
J0209_3 TRUE
J0209_2 FALSE
J0209_1 TRUE
###disable掉'j0209_1,j0209_2,j0209_3'三個job，使用TRANSACTIONAL Enable，結果沒有Job Enable
exec dbms_Scheduler.disable(name=>'j0209_1,j0209_2,j0209_3');
JOB_NAME ENABL
------------------------------ -----
J0209_3 FALSE
J0209_2 FALSE
J0209_1 FALSE
exec dbms_Scheduler.enable(name=>'j0209_1,j0209_2,j0209_3',commit_semantics=>'TRANSACTIONAL');
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');
JOB_NAME ENABL
------------------------------ -----
J0209_3 FALSE
J0209_2 FALSE
J0209_1 FALSE
////////////////////
// 20、 dbms_scheduler.get_file包的功能測試
////////////////////
dbms_scheduler.get_file可用於一般檔案的顯示及copy，也可以用於檢視及Copy具有credential的external job生成的日誌
###使用dbms_scheduler.get_file 直接輸出檔案內容
grant create external job to ad;
set serveroutput on
declare
v_fileout clob;
begin
dbms_lob.createtemporary(lob_loc=>v_fileout,cache=>FALSE,dur=>DBMS_LOB.SESSION);
dbms_scheduler.get_file(source_file=>'/home/oracle/bes.lic.txt',source_host=>NULL,credential_name=>'C0208_3',file_contents=>v_fileout);
dbms_output.put_line(v_fileout);
end;
/
###使用dbms_scheduler.get_file copy檔案至指定目錄
grant read,write on directory hisdmp to ad;
exec dbms_scheduler.get_file(source_file=>'/home/oracle/bes.lic.txt',source_host=>NULL,credential_name=>'C0208_3',destination_file_name=>'bes.lic.txt.hisdmp',destination_directory_object=>'hisdmp');
oracle@jq570322b:/home/oracle>ls -lrt /oradata01/hisdmp/monthly/bes.lic.txt.hisdmp
-rw-r--r-- 1 oracle oinstall 1438 Feb 09 15:15 /oradata01/hisdmp/monthly/bes.lic.txt.hisdmp
////////////////////
// 21、使用dbms_scheduler.drop_job drop job_class時，commit_semantics引數只能指定'STOP_ON_FIRST_ERROR'
////////////////////
####建立job
create or replace procedure prc0209_11(v_str in varchar2)
as
begin
dbms_output.put_line(v_str);
end;
/
exec dbms_scheduler.create_job_class(job_class_name=>'JC0209_1');
exec dbms_scheduler.create_job(job_name=>'J0209_11',job_class=>'JC0209_1',program_name=>'P0209_11',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);
exec dbms_scheduler.create_job(job_name=>'J0209_12',job_class=>'JC0209_1',program_name=>'P0209_11',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);
exec dbms_scheduler.create_program(program_name=>'P0209_11',program_type=>'STORED_PROCEDURE',program_action=>'PRC0209_11',number_of_arguments=>1);
exec dbms_scheduler.define_program_argument(program_name=>'P0209_11',argument_position=>1,argument_type=>'VARCHAR2');
exec dbms_scheduler.set_job_argument_value(job_name=>'J0209_11',argument_position=>1,argument_value=>'J0209_11');
exec dbms_scheduler.set_job_argument_value(job_name=>'J0209_12',argument_position=>1,argument_value=>'J0209_12');
exec dbms_scheduler.enable('P0209_11');
set serveroutput on
exec dbms_scheduler.run_job(job_name=>'J0209_11');
exec dbms_scheduler.run_job(job_name=>'J0209_12');
####drop job class
exec dbms_scheduler.drop_job(job_name=>'SYS.JC0209_1',commit_semantics=>'TRANSACTIONAL');
*
ERROR at line 1:
ORA-27361: scheduler API invoked with illegal or inconsistent arguments
ORA-06512: at "SYS.DBMS_ISCHED", line 6652
ORA-06512: at "SYS.DBMS_SCHEDULER", line 651
ORA-06512: at line 1
exec dbms_scheduler.drop_job(job_name=>'SYS.JC0209_1',commit_semantics=>'ABSORB_ERRORS');
*
ERROR at line 1:
ORA-27361: scheduler API invoked with illegal or inconsistent arguments
ORA-06512: at "SYS.DBMS_ISCHED", line 6652
ORA-06512: at "SYS.DBMS_SCHEDULER", line 651
ORA-06512: at line 1
---只能使用STOP_ON_FIRST_ERROR才成功
exec dbms_scheduler.drop_job(job_name=>'SYS.JC0209_1',commit_semantics=>'STOP_ON_FIRST_ERROR');
///////////////////////////////////////
/// 22、合理使用dbms_Scheduler.drop_job裡的defer引數避免對於正在執行的job進行drop_job操作時出錯
///////////////////////////////////////
create or replace procedure PRC0209_4
as
begin
while ( true ) loop
dbms_output.put_line('AAA');
dbms_lock.sleep(3);
end loop;
end;
/
exec dbms_scheduler.create_program(program_name=>'P0209_4',program_type=>'STORED_PROCEDURE',program_action=>'PRC0209_4');
exec dbms_scheduler.create_job(job_name=>'J0209_4',program_name=>'P0209_4',auto_drop=>FALSE);
exec dbms_scheduler.enable('P0209_4');
exec dbms_scheduler.run_job(job_name=>'J0209_4',use_current_session=>FALSE);
---drop job 不指定force和defer引數，預設就是force=>FALSE、defer=>FALSE，這種情況下不能drop一個正在執行的job
exec dbms_scheduler.drop_job('J0209_4');
BEGIN dbms_scheduler.drop_job('J0209_4'); END;
*
ERROR at line 1:
ORA-27478: job "AD.J0209_4" is running
ORA-06512: at "SYS.DBMS_ISCHED", line 213
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657
ORA-06512: at line 1
---drop job 不指定defer=>TRUE引數，這種情況下drop不會報錯，會等到job執行結束
exec dbms_scheduler.drop_job(job_name=>'J0209_4',defer=>TRUE);
PL/SQL procedure successfully completed.
SQL> select job_name from dba_scheduler_running_jobs;
JOB_NAME
------------------------------
J0209_4
---stop_job後，觀察這個job已經不存在了
exec dbms_scheduler.stop_job(job_name=>'J0209_4');
PL/SQL procedure successfully completed.
SQL> select job_name from dba_scheduler_jobs where job_name='J0209_4'
no rows selected
///////////////////
// 23、使用dbms_scheduler.disable禁用job時，如果force=>TRUE，那麼忽略commit_semantics的引數值
///////////////////
create or replace procedure PRC0210_1
as
begin
while ( true ) loop
dbms_output.put_line('AAA');
dbms_lock.sleep(3);
end loop;
end;
/
create or replace procedure PRC0210_2
as
begin
dbms_output.put_line('BBB');
dbms_lock.sleep(3);
end;
/
exec dbms_scheduler.create_program(program_name=>'P0210_1',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_1');
exec dbms_scheduler.create_program(program_name=>'P0210_2',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_2');
exec dbms_scheduler.create_job(job_name=>'J0210_1',program_name=>'P0210_1',repeat_interval=>'FREQ=MINUTELY',end_date=>sysdate+100/1440);
exec dbms_scheduler.create_job(job_name=>'J0210_2',program_name=>'P0210_2',repeat_interval=>'FREQ=MINUTELY;interval=10',end_date=>sysdate+100/1440);
exec dbms_scheduler.enable('P0210_1,P0210_2,J0210_1,J0210_2');
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0210_1','J0210_2');
--等J0210_1排程起來後，disable時提示job is running
exec dbms_scheduler.disable('J0210_1,J0210_2');
BEGIN dbms_scheduler.disable('J0210_1,J0210_2'); END;
*
ERROR at line 1:
ORA-24101: stopped processing the argument list at: J0210_1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2752
ORA-27478: job "AD.J0210_1" is running
ORA-06512: at line 1
select job_name from dba_Scheduler_running_jobs;
JOB_NAME
------------------------------
J0210_1
set linesize 180
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0210_1','J0210_2');
JOB_NAME ENABL
------------------------------ -----
J0210_1 TRUE
J0210_2 TRUE
--使用force=>TRUE，同時制定commit_semantics=>'STOP_ON_FIRST_ERROR'，disable成功
exec dbms_scheduler.disable(name=>'J0210_1,J0210_2',force=>TRUE,commit_semantics=>'STOP_ON_FIRST_ERROR');
PL/SQL procedure successfully completed.
---狀態變成enabled=FALSE了，但之前的job還在執行
select job_name,enabled from dba_scheduler_jobs where job_name in ('J0210_1','J0210_2')
JOB_NAME ENABL
------------------------------ -----
J0210_1 FALSE
J0210_2 FALSE
---但disable前發起的job仍在執行
SQL> select job_name from dba_Scheduler_running_jobs;
JOB_NAME
------------------------------
J0210_1
///////////////////
// 24、external job裡使用了credential_name，job執行時才會在$ORACLE_HOME/scheduler/log目錄下生成_stdout、_stderr檔案
///////////////////
###未使用credential_name的external job不會在在$ORACLE_HOME/scheduler/log目錄下生成job_X_stderr或者job_X_stdout檔案
oracle@jq570322b:/home/oracle>cat test.sh
#!/usr/bin/ksh
date > /home/oracle/test.log
date
chmod o+x test.sh
---建立job時不指定credential引數
exec dbms_scheduler.create_job(job_name=>'J0210_3',job_type=>'EXECUTABLE',job_action=>'/home/oracle/test.sh',repeat_interval=>'FREQ=SECONDLY;INTERVAL=30',end_date=>sysdate+100/1440);
exec dbms_scheduler.enable('J0210_3');
---目錄$ORACLE_HOME/scheduler/log下為空
###使用了credential_name的external job才能在$ORACLE_HOME/scheduler/log目錄下生成job_6427235_65634_stderr或者job_6427235_65634_stdout檔案
exec dbms_scheduler.create_credential(credential_name=>'C0210_3',username=>'oracle',password=>'uiop7890');
exec dbms_scheduler.stop_job('J0210_3');
exec dbms_scheduler.disable('J0210_3');
exec dbms_scheduler.set_attribute('J0210_3','credential_name','C0210_3');
exec dbms_scheduler.enable('J0210_3');
col log_date format a50
col job_name format a10
col additional_info format a60
col credential_name format a10
col status format a10
set linesize 180
set pagesize 200
set pause on
select job_name,credential_name,status,log_date,additional_info from dba_scheduler_job_run_details where job_name='J0210_3' order by log_date desc;
JOB_NAME CREDENTIAL STATUS LOG_DATE ADDITIONAL_INFO
---------- ---------- ---------- -------------------------------------------------- ------------------------------------------------------------
J0210_3 C0210_3 SUCCEEDED 10-FEB-15 11.53.12.071569 AM +08:00 EXTERNAL_LOG_ID="job_6427235_65646",
USERNAME="oracle",
STANDARD_ERROR="Permission denied
/home/oracle/test.sh[2]: /home/oracle/test.log: cannot creat
e
"
oracle@jq570322b:/oracle/app/oracle/product/11.2.0/db_1/scheduler/log>cat job_6427235_65646_stderr
Permission denied
/home/oracle/test.sh[2]: /home/oracle/test.log: cannot create
--修正上述許可權錯誤後，J0210_3重新執行，後生成_stdout檔案
oracle@jq570322b:/oracle/app/oracle/product/11.2.0/db_1/scheduler/log>ls -rlt job*_stdout
-rw-rw---- 1 oracle oinstall 29 Feb 10 12:03 job_6427235_65677_stdout
-rw-rw---- 1 oracle oinstall 29 Feb 10 12:04 job_6427235_65679_stdout
-rw-rw---- 1 oracle oinstall 29 Feb 10 12:04 job_6427235_65680_stdout
-rw-rw---- 1 oracle oinstall 29 Feb 10 12:05 job_6427235_65682_stdout
oracle@jq570322b:/oracle/app/oracle/product/11.2.0/db_1/scheduler/log>cat job_6427235_65682_stdout
Tue Feb 10 04:05:12 UTC 2015
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1438478/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Kubernetes scheduler學習筆記筆記
- oracle學習筆記Oracle筆記
- oracle 學習筆記 (ORACLE NET )Oracle筆記
- CUUG筆記 ORACLE索引學習筆記筆記Oracle索引
- 全面學習ORACLE Scheduler特性(7)Scheduler丟擲的EventsOracle
- oracle學習筆記《一》Oracle筆記
- Oracle學習筆記2Oracle筆記
- Oracle學習筆記1Oracle筆記
- ORACLE dataguard學習筆記Oracle筆記
- Oracle FlashBack 學習筆記Oracle筆記
- ORACLE學習筆記(zt)Oracle筆記
- Oracle學習筆記-1Oracle筆記
- Oracle DBA學習日記筆記Oracle筆記
- 某人的oracle9i學習筆記，與大家分享(zt)Oracle筆記
- oracle之awr學習筆記Oracle筆記
- Oracle基礎學習筆記Oracle筆記
- Oracle學習筆記之二Oracle筆記
- oracle 原理學習筆記（一）Oracle筆記
- 全面學習ORACLE Scheduler特性(1)建立jobsOracle
- 全面學習ORACLE Scheduler特性(2)管理jobsOracle
- 全面學習ORACLE Scheduler特性(3)使用ProgramsOracle
- oracle學習筆記--oracle常用的命令Oracle筆記
- oracle 學習筆記---效能優化學習(1)Oracle筆記優化
- 全面學習ORACLE Scheduler特性(7)使用Events之Scheduler丟擲的EventsOracle
- 大資料教程分享Actor學習筆記大資料筆記
- web前端培訓分享node學習筆記Web前端筆記
- Oracle學習筆記（6）——函式Oracle筆記函式
- oracle學習筆記-常用的命令Oracle筆記
- oracle學習筆記——檢視、索引Oracle筆記索引
- oracle9i學習筆記Oracle筆記
- 全面學習ORACLE Scheduler特性(9)建立ChainsOracleAI
- 全面學習ORACLE Scheduler特性(10)管理ChainsOracleAI
- 全面學習ORACLE Scheduler特性(11)使用Job ClassesOracle
- Oracle學習、進階資料合集（含教程、筆記、題庫下載與學習方法分享）Oracle筆記
- Quartz job scheduler 學習quartz
- numpy的學習筆記\pandas學習筆記筆記
- Oracle體系結構學習筆記Oracle筆記
- oracle學習筆記８: 分析函式Oracle筆記函式