通過案例學調優之--JOB管理
Oracle在創建和管理job主要借助兩個包，分別為DBMS_JOB和DBMS_SCHEDULER
[oracle@rh7 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 25 17:02:15 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. 11:03:43 SYS@ prod >show parameter job NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 1000 Job_queue_processes = 1000; 這個是運行JOB時候所起用的進程數，當然系統里面JOB大于這個數值后，就會有排隊等候的，最小值是0，表示不運行JOB，最大值是1000
11:26:14 SCOTT@ prod >desc dbms_job
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN PROCEDURE BROKEN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN BROKEN BOOLEAN IN NEXT_DATE DATE IN DEFAULT PROCEDURE CHANGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT PROCEDURE INSTANCE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN INSTANCE BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULT PROCEDURE INTERVAL Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN INTERVAL VARCHAR2 IN PROCEDURE ISUBMIT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT FUNCTION IS_JOBQ RETURNS BOOLEAN PROCEDURE NEXT_DATE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN NEXT_DATE DATE IN PROCEDURE REMOVE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN PROCEDURE RUN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULT PROCEDURE SUBMIT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER OUT WHAT VARCHAR2 IN NEXT_DATE DATE IN DEFAULT INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT PROCEDURE USER_EXPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUT PROCEDURE USER_EXPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUT MYINST VARCHAR2 IN/OUT PROCEDURE WHAT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN
DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列） 類型 描述 JOB NUMBER 任務的唯一標示號 LOG_USER VARCHAR2(30) 提交任務的用戶 PRIV_USER VARCHAR2(30) 賦予任務權限的用戶 SCHEMA_USER VARCHAR2(30) 對任務作語法分析的用戶模式 LAST_DATE DATE 最后一次成功運行任務的時間 LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小時，分鐘和秒 THIS_DATE DATE 正在運行任務的開始時間，如果沒有運行任務則為null THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小時，分鐘和秒 NEXT_DATE DATE 下一次定時運行任務的時間 NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小時，分鐘和秒 TOTAL_TIME NUMBER 該任務運行所需要的總時間，單位為秒 BROKEN VARCHAR2(1) 標志參數，Y標示任務中斷，以后不會運行 INTERVAL VARCHAR2(200) 用于計算下一運行時間的表達式 FAILURES NUMBER 任務運行連續沒有成功的次數 WHAT VARCHAR2(2000) 執行任務的PL/SQL塊 CURRENT_SESSION_LABEL RAW MLSLABEL 該任務的信任Oracle會話符 CLEARANCE_HI RAW MLSLABEL 該任務可信任的Oracle最大間隙 CLEARANCE_LO RAW MLSLABEL 該任務可信任的Oracle最小間隙 NLS_ENV VARCHAR2(2000) 任務運行的NLS會話設置 MISC_ENV RAW(32) 任務運行的其他一些會話參數
創建job：
DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER, --job參數的名稱（上例的test_job） what IN VARCHAR2, --執行的存儲過程 next_date IN DATE DEFAULT sysdate, --何時運行這個job interval IN VARCHAR2 DEFAULT 'null', --何時這個job被從新執行 no_parse IN BOOLEAN DEFAULT FALSE, --是否從新解析 instance IN BINARY_INTEGER DEFAULT any_instance, --指定哪個實例去執行（用在RAC環境下） force IN BOOLEAN DEFAULT FALSE --是否必須由執行的實例才能執行 );
案例測試：
1、建立測試表
117:17:45 SCOTT@ prod >CREATE TABLE DATE_LOG 17:18:03 2 (create_date DATE CONSTRAINT create_date_pk PRIMARY KEY); Table created. Elapsed: 00:00:01.15 17:18:06 SCOTT@ prod >desc date_log Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- CREATE_DATE NOT NULL DATE
2、建立一個procedure
17:18:15 SCOTT@ prod >CREATE OR REPLACE PROCEDURE create_date_log_row 17:18:27 2 IS 17:18:27 3 BEGIN 17:18:27 4 INSERT INTO date_log(create_date)VALUES(sysdate); 17:18:27 5 END; 17:18:27 6 / Procedure created.
3、建立一個job
17:40:02 SCOTT@ prod >begin 17:40:23 2 dbms_job.submit(:jobno,'create_date_log_row;',sysdate,'sysdate+1/1440'); 17:40:31 3 commit; 17:40:35 4 end; 17:40:36 5 / PL/SQL procedure successfully completed.
4、查看測試表情況
17:41:31 SCOTT@ prod >select * from DATE_LOG; CREATE_DA --------- 25-AUG-14 25-AUG-14 25-AUG-14 25-AUG-14
5、查看job的工作情況
17:42:28 SCOTT@ prod >col interval for a50 17:42:36 SCOTT@ prod >r 1 select last_date,last_sec,next_date,next_sec,interval,broken from user_jobs 2* where job=24 LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL B --------- ---------------- --------- ---------------- -------------------------------------------------- - 25-AUG-14 17:41:41 25-AUG-14 17:42:41 sysdate+1/1440 N LAST_DATE 第一次執行日期 LAST_SEC 第一次執行時間 NEXT_DATE 下一次執行日期 NEXT_SEC 下一次執行時間 INTERVAL 執行頻率
6、中止和啟動job的執行
17:43:22 SCOTT@ prod >exec dbms_job.broken(24,true); PL/SQL procedure successfully completed. 17:43:45 SCOTT@ prod >commit; //注意要提交 Commit complete. 查看中斷信息： 17:43:47 SCOTT@ prod >select broken from user_jobs where job = 24; B - Y 啟動job： 17:44:00 SCOTT@ prod >exec dbms_job.broken(24,false); PL/SQL procedure successfully completed. 17:44:35 SCOTT@ prod >select broken from user_jobs where job = 24; B - N
7、修改job的執行時間
11:17:40 SCOTT@ prod >select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT ---------- ---------- --------- ---------------- --------- ---------------- ---------- ---------- 23 SCOTT 26-AUG-14 11:17:40 26-AUG-14 11:18:40 sysdate+1/ create_dat 1440 e_log_row; 24 SCOTT 26-AUG-14 11:10:39 26-AUG-14 11:18:39 sysdate+3/ create_dat (24*60) e_log_row;
查詢目前下次執行時間 11:17:40 SCOTT@ prod >select next_date,next_sec from user_jobs; NEXT_DATE NEXT_SEC --------- ---------------- 26-AUG-14 11:20:40 26-AUG-14 11:21:40 修改下次執行之時間 SQL> exec dbms_job.next_date(24,sysdate+2/(24*60)); SQL>commit; 查詢目前下次執行時間 SQL> select next_date,next_sec from user_jobs; NEXT_DATE NEXT_SEC ---------- ---------------- 26-AUG-14 12:05:58 第五步 修改job執行頻率 SQL> exec dbms_job.interval(24,'sysdate+3/(24*60)'); SQL>commit; 查詢執行頻率 SQL> select interval from user_jobs; INTERVAL -------------------------------------- sysdate+3/(24*60)
8、刪除job
SQL> exec dbms_job.REMOVE(24); SQL> commit; 11:25:33 SCOTT@ prod >exec dbms_job.remove(24); PL/SQL procedure successfully completed. 11:25:49 SCOTT@ prod >commit; Commit complete.
附錄：
其中的Interval的設置是一個難點，在此闡述幾個常用的設置值：
1、 每分鐘執行 Interval => TRUNC(sysdate,’mi’) + 1 / (24*60) 2、 每天定時執行 例如：每天的凌晨2點執行 Interval => TRUNC(sysdate) + 1 +2 / (24) 3、 每周定時執行 例如：每周一凌晨2點執行 Interval => TRUNC(next_day(sysdate,'星期一'))+2/24 4、 每月定時執行 例如：每月1日凌晨2點執行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24 5、 每季度定時執行 例如每季度的第一天凌晨2點執行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 6、 每半年定時執行 例如：每年7月1日和1月1日凌晨2點 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 7、 每年定時執行 例如：每年1月1日凌晨2點執行 Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 8、 每天午夜12點 'TRUNC(SYSDATE + 1)' 9、 每天早上8點30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)' 10、 每星期二中午12點 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24' 11、 每個月第一天的午夜12點 'TRUNC(LAST_DAY(SYSDATE ) + 1)' 12、 每個季度最后一天的晚上11點 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24' 13、 每星期六和日早上6點10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "S UNDAY"))) + （6×60+10）/（24×60）'
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
