现在的位置: 首页 > 数据库 > 正文

Oracle JOB定时自动执行存储过程

2014年12月28日 数据库 ⁄ 共 5213字 暂无评论 ⁄ 阅读 4,202 次
文章目录

在报表分析等OLAP中,有些过程是需要定时执行的。此时,我们可以借助Oracle提供的包DBMS_JOB来调试PL/SQL过程的定时自动执行。

DBMS_JOB包的主要过程

  • submit(job,what,next_date,interval[,no_parse]):向作业队列新增作为作业的PL/SQL过程,即创建一个JOB。它有5个参数JOB、WHAT、NEXT_DATE、INTERVAL和NO_PRARSE。其中参数JOB是输出参数(只需要一个变量用于接收输出值),查询指定作业JOB值可使用视图user_jobs。其他4个参数是输入参数,WHAT参数指定要添加到JOB队列中的代码块(通常是存储过程名),NEXT_DATE指定该JOB何时被执行,INTERVAL指定JOB的重复执行间隔时间,NO_PARSE指定此JOB提交或执行时是否进行语法分析,默认值为false(一般省略该参数,即使用默认值)。
  • isubmit:类似submit,区别在于可自定义JOB号,若JOB号已存在则报错。即该过程中第一个参数JOB是IN模式的参数,而不是submit过程中OUT模式。
  • remove(job):从作业队列中删除已提交过的PL/SQL过程,即删除一个JOB。该过程仅一个参数job(查job值使用user_jobs视图),它是submit创建job时系统生成的唯一值,正在运行的JOB不能删除。
  • change(job,what,next_date,interval):更改已提交某作业的参数,如下次执行时间及执行时间间隔等。4个参数均为IN模式,job值唯一标识了一个JOB,what为此JOB运行的PL/SQL块,next_date指定JOB下次执行时间,interval指定重复执行时间间隔。
  • broken(job,broken[,next_date]):更改已提交某作业的状态,可禁止作业队列中某JOB的执行。job是在submit时生成的唯一值;broken为boolean类型,TRUE表示不再执行,FALSE表示执行;next_date指定该JOB何时开始再次运行,默认值为当前时间。
  • next_date(job,next_date):更改已提交某作业的下一次执行时间,job为submit提供的唯一值,next_date指示此JOB下次执行的时间。
  • interval(job,interval):更改已提交某作业的执行时间间隔,job为submit提供的唯一值,interval指定重复执行时间间隔。
  • run(job):立即执行指定JOB,job为submit提供的唯一值,可使用user_jobs视图查询。

创建JOB

方法一:使用PL/SQL在对象目录DBMS_Jobs上点击右键->新建,在弹出界面填写what、next_date和intervel参数创建。

方法二:使用匿名过程直接执行dbms_jobs.submit()过程,如:

declare

variable job number;

begin

sys.dbms_job.submit(:job,'prc_name;',to_date('22-11-2013 09:09:41', 'dd-mm-yyyy hh24:mi:ss'),'sysdate+1/86400');--每天86400秒钟,即一秒钟运行prc_name过程一次

commit;

end;

JOB定时执行

1)固定间隔执行

每半小时执行一次:

sysdate + (30*60)/(24*60*60)或:sysdate + 30/(24*60)

或:trunc(sysdate,’mi’) + 30/(24*60)

每3天执行一次:

sysdate +3

2)每天定时执行

每天午夜12点:

trunc(sysdate + 1),注:少了“+1”则时间就变成之前的了,而不是JOB执行之后的某个时间

每天早上8点30分:

trunc(sysdate + 1) + (8*60 + 30)/(24*60)

3)每周定时执行

每周一凌晨3点:

trunc(next_day(sysdate,2)) + 3/24,注:每周的第2天是周一,这里不需要“+1”,因为使用next_day()函数得到的肯定是未来的某个时间而不会是之前的时间

4)每月定时执行

每月1号2点:

trunc(last_day(sysdate)+1) + 2/24

5)每季度定时执行

每季度第1天凌晨1点:

trunc(add_months(sysdate,3),’Q’) + 1/24

6)每半年定时执行

每年7月1日和1月1日凌晨1点:

add_months(trunc(sysdate,’yyyy’),6)+1/24

7)每年定时执行

每年1月1日凌晨1点:

add_months(trunc(sysdate,’yyyy’),12) + 1/24

查看JOB相关信息

JOB相关信息的视图有dba_jobs、all_jobs和user_jobs,dba_jobs_running包含正在运行的JOB的信息。

查看所有JOB:

select job,what,next_date,interval,broken from dba_jobs;

查看正在运行JOB:

select t1.sid,t1.job,t2.log_user,t2.this_date from dba_jobs_running t1,dba_jobs t2 where t1.job = t2.job;

常用参数:

字段(列)          类型                 描述

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任务数据和控制并发

并发JOB数设置:

通过job_queue_processes参数可以设置OR能够并发的job数量,在sqlplus中可使用命令查看当前并发数:

show parameter job_queue_process;

修改当前并发数:

alter system set job_queue_processes = 39 scope = spfile;//最大值不能超过1000;

job_queue_interval = 10; //调度作业刷新频率秒为单位

当job_queue_process值为0时表示全部停止oracle的job,可以通过语句

alter system set job_queue_processes = 10;来调整启动oracle的job。

如果将job_queue_processes 的值设置为1的话,那就是串行运行,即快速切换执行一个job任务。

JOB不运行的原因

1)并行JOB数超限:job_queue_processes参数设定JOB最大运行进程数,当然系统里面JOB大于这个数值后,就会有排队等候的,最小值是0,表示不运行JOB,最大值是1000,在OS上对应的进程时SNPn,9i以后OS上管理job的进程叫CJQn。可以使用下面这个SQL确定目前有几个SNP/CJQ在运行。

select * from v$bgprocess,这个paddr不为空的snp/cjq进程就是目前空闲的进程,有的表示正在工作的进程。

另外一个是job_queue_interval,范围在1--3600之间,单位是秒,这个是唤醒JOB的process,因为每次snp运行完他就休息了,需要定期唤醒他,这个值不能太小,太小会影响数据库的性能。

先确定上面这两个参数设置是否正确,特别是第一个参数,设置为0了,所有job就不会自动运行了。

2)使用下面的SQL查看job的的broken,last_date和next_date,last_date是指最近一次job运行成功的结束时间,next_date是根据设置的频率计算的下次执行时间,根据这个信息就可以判断job上次是否正常,还可以判断下次的时间对不对,SQL如下:

select * from dba_jobs;

有时候我们发现他的next_date是4000年1月1日,说明job要不就是在running,要不就是状态是break(broken=Y),如果发现job的broken值为Y,找用户了解一下,确定该job是否可以broken,如果不能broken,那就把broken值修改成N,修改再使用上面的SQL查看就发现它的last_date已经变了,job即可正常运行,修改broken状态的SQL如下:

begin

DBMS_JOB.BROKEN(<JOB_ID>, FALSE);

end;

3)使用下面的SQL查询是否job还在running

select * from dba_jobs_running;

如果发现job已经Run了很久了还没有结束,就要查原因了。一般的job running时会锁定相关的相关的资源,可以查看一下v$access和v$locked_object这两个view。如果发现其他进程锁定了与job相关的object,包括package/function/procedure/table等资源,那么就要把其他进程删除,有必要的话,把job的进程也删除,再重新执行看看结果。

4)如果上面都正常,但是job还不run,怎么办?那我们要考虑把job进程重启一次,防止是SNP进程死了造成job不跑,指令如下:

alter system set job_queue_processes = 0; --关闭job进程,等待5--10秒钟

alter system set job_quene_processes = 5; --恢复原来的值

5)Oracle的BUG:Oracle9i里面有一个BUG,当计数器到497天时,刚好达到它的最大值,再计数就会变成-1,继续计数就变成0了,然后计数器将不再跑了。如果碰到这种情况就得重启数据库,但是其他的Oracle7345和Oracle8i的数据库没有发现这个问题。

6)数据库上的检查基本上就这多,如果job运行还有问题,那需要看一下是否是程序本身的问题,比如处理的资料量大,或者网络速度慢等造成运行时过长,那就需要具体情况具体分析了。我们可以通过下面的SQL手工执行一下job看看:

begin

dbms_job.run(<job>_ID)

end;

如果发现job执行不正常,就要结合程序具体分析一下。

 

原文地址:http://www.cnblogs.com/hoojo/p/oracle_procedure_job_interval.html

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?