通过案例学调优之--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进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
