通过案例学调优之--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，最大值是100011: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 INDBA_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 DATE2、建立一个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-145、查看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
-
N7、修改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）'
