首页 > 代码库 > JOB
JOB
1.1 描述
定时执行数据库中的PL/SQL块,如存储过程。可以减化前端编程。
1.2 简单示例
本示例运行环境:oracle10g + pl/sql +xp。
使用存储过程每一分钟为一张表添加一条记录。
- 创建序列
create sequence student_id minvalue 10 maxvalue 999999 start with 10 increment by 1 cache 2 cycle order; |
- 创建存储过程
create or replace procedure p_test_job as begin insert into student(sid) values (student_id.nextval); commit; end; |
- 创建JOB
命令行创建
variable test_job number; begin sys.dbms_job.submit(job => :test_job, --对应的唯一id(jobId<->jobName)唯一映射 what => ‘p_test_job;‘, --调用的存储过程 next_date => sysdate, --第一次执行时间 interval => ‘sysdate+1/1440‘);--执行间隔 commit; end; / |
说明:该语句需要在命令窗口中执行,begin至/的部分一行一行写入命令行时,会报错。复制后一次粘贴到命令行时就没有问题。
PL/SQL创建
declare test_job number; begin sys.dbms_job.submit(test_job, ‘p_test_job;‘, sysdate, interval => ‘sysdate+1/1440‘); commit; end; |
test_job的值是submit过程返回回来的。Submit()的括号中不能写注释。
- 执行JOB
begin dbms_job.run(:test_job); --执行job end; / |
立即执行JOB,而不是按计划执行。
- 查看创建的JOB
select * from sys.user_jobs; |
输出结果:
- 停止JOB
begin sys.dbms_job.broken(21,true); end; / |
参数true为停止,false为启动。
- 启动JOB
begin sys.dbms_job.broken(21,false); end; |
- 删除JOB
begin dbms_job.remove(21); --删除job end; / |
1.3 常用参数
序号 | 字段 | 类型 | 说明 |
1 | JOB | NUMBER | 任务的唯一标示号 |
2 | LOG_USER | VARCHAR2(30) | 提交任务的用户 |
3 | PRIV_USER | VARCHAR2(30) | 赋予任务权限的用户 |
4 | SCHEMA_USER | VARCHAR2(30) | 对任务作语法分析的用户模式 |
5 | LAST_DATE | DATE | 最后一次成功运行任务的时间 |
6 | LAST_SEC | VARCHAR2(8) | 如HH24:MM:SS格式的last_date日期的小时,分钟和秒 |
7 | THIS_DATE | DATE | 正在运行任务的开始时间,如果没有运行任务则为null |
8 | THIS_SEC | VARCHAR2(8) | 如HH24:MM:SS格式的this_date日期的小时,分钟和秒 |
9 | NEXT_DATE | DATE | 下一次定时运行任务的时间 |
10 | NEXT_SEC | VARCHAR2(8) | 如HH24:MM:SS格式的next_date日期的小时,分钟和秒 |
11 | TOTAL_TIME | NUMBER | 该任务运行所需要的总时间,单位为秒 |
12 | BROKEN | VARCHAR2(1) | 标志参数,Y表示任务中断,以后不会运行;N表示任务正常。 |
13 | INTERVAL | VARCHAR2(200) | 用于计算下一运行时间的表达式 |
14 | FAILURES | NUMBER | 任务运行连续没有成功的次数 |
15 | WHAT | VARCHAR2(2000) | 执行任务的PL/SQL块 |
16 | CLEARANCE_HI | RAW | 该任务可信任的Oracle最大间隙 |
17 | CLEARANCE_LO | RAW | MLSLABEL |
18 | NLS_ENV | VARCHAR2(2000) | 任务运行的NLS会话设置 |
19 | MISC_ENV | RAW(32) | 任务运行的其他一些会话参数 |
20 | CURRENT_SESSION_LABEL | RAW MLSLABEL | 该任务的信任Oracle会话符 |
1.4 几个调用方法
1.4.1 Submit创建job
定义
PROCEDURE Submit ( job OUT binary_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)
参数说明
job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。
what参数 是将被执行的PL/SQL代码块; 或者为计划任务名,如果是多个计划任务,就都用分号隔开 。
next_date参数 指识何时将运行这个工作。
interval参数 何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE
指示此PL/SQL代码在它第一次执行时应进行语法分析,
而FALSE指示本PL/SQL代码应立即进行语法分析。
如
declare v_job number :=1; begin dbms_job.submit(v_job,‘dbms_output.put_line(1);‘,sysdate,‘sysdate+1/1440‘); commit; end; |
1.4.2 其他方法
dbms_job.run(v_job); --立即运行job
dbms_job.broken(v_job,true,next_date); --停止一个job,,next_date(某一时刻停止)也可是sysdate(立刻停止)。
dbms_job.remove(v_job); --删除某个job
dbms_job.what(v_job,‘sp_fact_charge_code;‘); --修改某个job名
dbms_job.next_date(v_job,sysdate); --修改下一次运行时间 |
停止其他用户的JOB
begin
sys.dbms_ijob.broken(22,true); commit; end; |
以执行命令的方式启动
SQL> exec dbms_job.broken(22,false);
PL/SQL procedure successfully completed
|
需要提交。
1.5 查询JOB
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息。 通常不查询该表,该表查询效率较低。
如:
select * from dba_jobs
1.6 频率设置
Trunc(Sysdate+1)是明天的日期,但Job频率的设置是指下一次的执行时间。
序号 | 时间 | 设置 |
1 | 每天8点10分 | Trunc(Sysdate+1) + (8*60+10)/24*60 |
2 | 每个小时的第15分钟(如8:15,9:15,10:15) | trunc(sysdate,‘hh‘)+75/1440 |
3 | 半个小时 | sysdate+30/1440 |
4 | 每天6点 | trunc(sysdate+1)+6/24 |
5 | 每天 | trunc(sysdate+1) |
6 | 每周 | trunc(sysdate+7) |
7 | 每个星期日 | next_day(trunc(sysdate),‘SUNDAY‘) |
8 | 每月 | trunc(sysdate+30) |
9 | 每5秒执行一次 | sysdate+1/86400 |
10 | 每15秒执行一次 | sysdate+1/8640 |
11 | 每星期二中午12点 | NEXT_DAY(TRUNC(SYSDATE ), ‘‘TUESDAY‘‘ ) + 12/24 |
12 | 每个月第一天的午夜12点 | TRUNC(LAST_DAY(SYSDATE ) + 1) |
13 | 每个季度最后一天的晚上11点 | TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q‘ ) -1/24 |
14 | 每星期六和日早上6点10分 | TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘‘SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60) |
其他
1:每分钟执行 Interval => TRUNC(sysdate,‘mi‘) + 1/ (24*60)
2:每天定时执行 例如:每天的凌晨1点执行 Interval => TRUNC(sysdate) + 1 +1/ (24)
3:每周定时执行 例如:每周一凌晨1点执行 Interval => TRUNC(next_day(sysdate,‘星期一‘))+1/24
4:每月定时执行 例如:每月1日凌晨1点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行 例如每季度的第一天凌晨1点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q‘) + 1/24
6:每半年定时执行 例如:每年7月1日和1月1日凌晨1点 Interval => ADD_MONTHS(trunc(sysdate,‘yyyy‘),6)+1/24
7:每年定时执行 例如:每年1月1日凌晨1点执行 Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy‘),12)+1/24
|
1.7 参数设置
1.7.1 job_queue_process
oracle能够并发的job的数量。
l 查看job_queue_process
SQL> show parameter job_queue_process;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 10 |
job_queue_process默认值为10,即同一时刻最多10个job运行。当job数目超过该参数时,就会进入等待队列。查看该参数需要有dba权限。
l 修改job_queue_process
SQL> alter system set job_queue_processes=39 scope=spfile;
System altered |
最大值不能超过1000;当job_queue_process值为0时表示全部停止oracle的job。
这样修改oracle中job_queue_process的值是动态的,数据库重启后,将从admin\db\pfile的init文件中读取。
1.8 JOB不运行原因排查
1.8.1 job_queue_processes
当数据库JOB的数目超过job_queue_processes的值时,JOB就会排除等待。使用具有DBA权限的用户查看数据库正在执行的进程。
SQL> select * from v$bgprocess;
PADDR PSERIAL# NAME DESCRIPTION ERROR -------- ---------- ----- ----------------------------------------------- ---------- 31A478FC 1 PMON process cleanup 0 00 0 DIAG diagnosibility process 0 |
有的JOB可以瞬间执行完成,有的则需要一段时间。
另外一个是job_queue_interval,范围在1--3600之间,单位是秒,这个是唤醒JOB的process,因为每次snp运行完他就休息了,需要定期唤醒他,这个值不能太小,太小会影响数据库的性能。 |
job_queue_interval参数可以不设置。
1.8.2 查看dba_jobs表
select * from user_jobs |
输出:
broken
Y : 表示该任务为停止状态
N : 表示该任务为启动状态
last_date
最近一次job运行成功的结束时间。
l next_date
根据设置的频率计算的下次执行时间。该时间并不一定是任务实际执行的时间,比如当任务实际执行的时间超过了任务之间的时间时。
1.8.3 查看任务是否仍然在执行
select * from dba_jobs_running |
需要DBA权限。
如果一直在跑,就需要查找原因,比如是否锁表等。查看v$access和v$locked_object可查看锁表对象。
1.8.4 重启JOB
alter system set job_queue_processes=0 --关闭job进程,等待5--10秒钟
alter system set job_quene_processes=5 --恢复原来的值 |
找不到原因时,可以重启一下试试。这样的重启是面向所有的JOB的。
1.8.5 手工执行JOB
declare
begin
dbms_job.run(<job>_ID);
end;
|
这样可以排查是否程序本身有错。
1.8.6 Oracle9i计数器
Oracle9i里面有一个BUG,当计数器到497天时,刚好达到它的最大值,再计数就会变成-1,继续计数就变成0了,然后计数器将不再跑了。如果碰到这种情况就得重启数据库。