Oracle定时任务Job

存储过程 ①：

• create or replace procedure up_mid_66880002(
•   v_rq         number,           --计算日期
•   rc1          out  bssys.tp_cursor,
•   rc2          out  bssys.tp_cursor
• )  
• as
• .......
• begin
• ......
• end up_mid_66880002;

存储过程②：

• create or replace procedure up_mid_66880003
• as
• .......
• begin
• ......
• end up_mid_66880003;

创建Job任务①（有参引用）：

declare job number;  --定义变量

begin

  sys.dbms_job.isubmit(job => job,--系统生成job号（系统指定）

              what => 'declare v_rq number; rc1 bssys.tp_cursor; rc2 bssys.tp_cursor; begin up_mid_66880002(v_rq,rc1,rc2);end;',--调用存储过程

              next_date => to_date('10-09-2016 16:34:00', 'dd-mm-yyyy hh24:mi:ss'),--设置下一次启动时间

              interval => 'TRUNC(sysdate,''mi'') + 10/ (24*60)');--设置时间间隔

  commit;

end;

/

如果需要自定义job号，把submit改为isubmit：

begin

  sys.dbms_job.isubmit(job => 1002,--自定义job号（用户指定）

              what => 'declare v_rq number; rc1 bssys.tp_cursor; rc2 bssys.tp_cursor; begin up_mid_66880002(v_rq,rc1,rc2);end;',--调用存储过程

              next_date => to_date('10-09-2016 16:34:00', 'dd-mm-yyyy hh24:mi:ss'),--设置下一次启动时间

              interval => 'TRUNC(sysdate,''mi'') + 10/ (24*60)');--设置时间间隔

  commit;

end;

/

创建job任务②（无参引用）：

declare job number;  --定义变量

begin

  sys.dbms_job.isubmit(job => job,--系统生成job号（系统指定）

              what => 'up_mid_66880002;',--调用存储过程

              next_date => to_date('10-09-2016 16:34:00', 'dd-mm-yyyy hh24:mi:ss'),--设置下一次启动时间

              interval => 'TRUNC(sysdate,''mi'') + 10/ (24*60)');--设置时间间隔

  commit;

end;

/

-----------------------------------------------------------相关资料------------------------------------------------------

DBMS_JOB包

DBMS_JOB包中包含有许多过程和方法

名称	类型	描述
DBMS_JOB.ISUBMIT	过程	提交一个新任务，用户指定一个任务号 dbms_job.isubmit ( job       IN BINARY_INTEGER, what      IN VARCHAR2, next_date IN DATE, interval  IN VARCHAR2 DEFAULT 'NULL', no_parse  IN BOOLEAN DEFAULT FALSE); *no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE) exec dbms_job.isubmit(100, 'begin MYPROC;end;', SYSDATE);
DBMS_JOB.SUBMIT	过程	提交一个新任务，系统指定一个任务号 dbms_job.submit( JOB       OUT BINARY_INTEGER, WHAT      IN  VARCHAR2,  NEXT_DATE IN  DATE           DEFAULT SYSDATE,  INTERVAL  IN  VARCHAR2       DEFAULT 'NULL', NO_PARSE  IN  BOOLEAN        DEFAULT FALSE, INSTANCE  IN  BINARY_INTEGER DEFAULT 0, FORCE     IN  BOOLEAN        DEFAULT FALSE);
DBMS_JOB.REMOVE	过程	从队列中删除一个已经存在的任务 dbms_job.removejob IN BINARY_INTEGER);
DBMS_JOB.CHANGE	过程	更改用户设定的任务参数 dbms_job.change( job       IN BINARY_INTEGER, what      IN VARCHAR2, next_date IN DATE, interval  IN VARCHAR2, instance  IN BINARY_INTEGER DEFAULT NULL, force     IN BOOLEAN        DEFAULT FALSE); exec dbms_job.change(100,NULL,NULL,'SYSDATE+3');
DBMS_JOB.WHAT	过程	更改PL/SQL任务定义 dbms_job.what (  job  IN BINARY_INTEGER, what IN VARCHAR2);
DBMS_JOB.NEXT_DATE	过程	更改任务下一次运行时间 dbms_job.next_date(  job       IN BINARY_INTEGER, next_date IN DATE);
DBMS_JOB.INTERVAL	过程	更改任务运行的时间间隔 dbms_job.interval (  job      IN BINARY_INTEGER, interval IN VARCHAR2); exec dbms_job.interval(100,'TRUNC(SYSDATE)+1');
DBMS_JOB.BROKEN	过程	将任务挂起，不让其重复运行，也可以重新启动任务。 dbms_job.broken (  job       IN BINARY_INTEGER, broken    IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE); exec dbms_job.broken(100,FALSE,NEXT_DAY(SYSDATE, 'MONDAY'));
DBMS_JOB.RUN	过程	在当前会话中立即执行任务。如果任务是broken状态，改为not broken. dbms_job.run(  job   IN BINARY_INTEGER, force IN BOOLEAN DEFAULT FALSE);
DBMS_JOB.INSTANCE	过程	用于RAC环境，为JOB的执行分配一个实例。 dbms_job.instance( job      IN BINARY_INTEGER, instance IN BINARY_INTEGER,  force    IN BOOLEAN DEFAULT FALSE); select instance_number from gv$instance; exec dbms_job.instance(100, 1);
DBMS_JOB.USER_EXPORT	过程	创建文字字符串，用于重新创建一个任务 dbms_job.user_export (  job    IN     BINARY_INTEGER, mycall IN OUT VARCHAR2); declare   l_sqltxt varchar2(1000); begin   dbms_job.user_export(100,l_sqltxt);   dbms_output.put_line(l_sqltxt); end;

注意：执行上面的任何创建/删除/更改任务的过程后一定要马上commit。

          You must issue a COMMIT statement immediately after the statement.

          否则不仅会造成更改无效，甚至很有可能造成一些奇怪的现象，比如job停止之类，切记！

参数说明

DBMS_JOB包中所有的过程都有一组相同的公共参数。

1）job

参数job是一个整数，用来唯一地标示一个任务。

该参数既可由用户指定也可由系统自动赋予。

DBMS_JOB.SUBMIT过程通过获得序列SYS.JOBSEQ的下一个值来自动赋予一个任务号，

该任务号是作为一个OUT参数返回的，所以调用者随后可以识别出提交的任务。

DBMS_JOB.ISUBMIT过程则由调用者给任务指定一个识别号，

如果指定了重复任务号，ISUBMIT过程会抛出一个错误。

当数据库被导出或者被导入的时候，任务号也将被保留下来。

以在执行含有任务的数据的导入/导出操作时很可能会发生任务号冲突的现象。

2）what

what参数是一个可以转化为合法PL/SQL调用的字符串，该调用将被任务队列自动执行。

what参数的长度是4000个字节。

该参数的值一般情况下都是对一个PL/SQL存储过程的调用。PL/SQL调用必须用分号隔开。

还有一个实际经验就是最好将存储过程调用封装在一个匿名块中，这样可以避免一些比较莫名错误的产生。

例如，一般情况下，what参数可以这样引用：what =>’my_proc;’ 

但是比较安全的引用，应该这样写：what =>’begin my_proc; end;’ 

任何时候，我们只要通过更改what参数就可以达到更改任务定义的目的。

但是需要注意，通过改变what参数来改变任务定义时，用户当前的会话设置也被记录下来并成为任务运行环境的一部分。

如果当前会话设置和最初提交任务时的会话设置不同，就有可能改变任务的运行行为。

3）next_date

Next_date参数是用来调度任务队列中该任务下一次运行的时间。

这个参数对于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN这两个过程确省为系统当前时间，也就是说任务将立即运行。

当将一个任务的next_date参数赋值为null时，则该任务下一次运行的时间将被指定为4000年1月1日，

也就是说该任务将永远不再运行。

如果想在任务队列中保留该任务而又不想让其运行，将next_date设置为null是一个非常简单的办法。

next_date也可以设置为过去的一个时间。系统任务的执行顺序是根据它们下一次的执行时间来确定的，

于是将next_date参数设置为过去就可以达到将该任务排在任务队列前面的目的。

4）Interval

Internal参数是一个表示Oracle合法日期表达式的字符串。

这个日期字符串的值在每次任务被执行时算出，

强调一点，next_date是在一个任务开始时算出的，而不是在任务成功完成时算出的。

算出的日期表达式有两种可能，要么是未来的一个时间要么就是null。

当任务成功完成时，系统更新任务队列表，将下一次任务要运行的时间置为前面算出的next_date值。

当由interval表达式算出next_date是null时，任务自动从任务队列中移出，不会再继续执行。

因此，如果传递一个null值给interval参数，则该任务仅仅执行一次。

JOB_QUEUE_PROCESSES

Oracle有专门的后台进程来执行任务队列。

后台进程的数量通过JOB_QUEUE_PROCESSES来设置，可以设为0到1000。

*JOB_QUEUE_INTERVAL从9i之后就被废弃了。

需要注意，如果配置了最多1 000 个Jnnn进程，并不会看到真的有1 000 个进程随数据库启动。

相反，开始时只会启动一个进程，即作业队列协调器(CJQ0)，

它在作业队列表中看到需要运行的作业时，会启动Jnnn 进程。

如果Jnnn 进程完成其工作，并发现没有要处理的新作业，此时Jnnn 进程就会退出。

unix下面可以用ps -ef | grep ora_j 查看这些进程(J000, ... J999)，

另外还有一个ora_cjq0_xxx的队列管理进程。

作业队列协调器(CJQ0)进程被唤醒时，

它首先查看任务队列目录中所有的任务是否当前的时间超过了下一次运行的日期时间。

检测到需要该时间立即执行的任务后，这些任务按照下一次执行日期的顺序依次执行。

当开始执行一个任务时，其过程如下：

1. 以任务所有者的用户名开始一个新的数据库会话。
2. 当任务第一次提交或是最后一次被修改时，更改会话NLS设置和目前就绪的任务相匹配。
3. 通过interval日期表达式和系统时间，计算下一次执行时间。
4. 执行任务定义的PL/SQL
5. 如果运行成功，任务的下一次执行日期（next_date）被更新，否则，失败计数加1。

任务运行失败后的重试

提交的任务如果运行失败会在2分钟后将再次试图运行该任务。

如果这次运行又失败了，下一次尝试将在4分钟后进行，再下一次在8分钟以后。

任务队列每次加倍重试间隔直到它超过了正常的运行间隔。

所以如果任务的运行间隔设置的比较短，比如1分钟，就观察不到这个重试时间间隔加倍现象。

在连续16次失败后，任务就被标记为中断的（broken），任务将不再重复执行。

这个时候，如果通过broken或者run等过程重新启动任务，成功之行一次后，这个失败间隔会清空。

如果重新启动后还是失败，那么失败次数变成17，job状态变回broken。

相关数据字典

任务队列中的任务信息可以通过以下几个字典视图来查看。

视图名	描述
DBA_JOBS	本数据库中定义到任务队列中的任务
DBA_JOBS_RUNNING	目前正在运行的任务
USER_JOBS	当前用户拥有的任务

DBA_JOBS 和USER_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)	任务运行的其他一些会话参数

DBA_JOBS_RUNNING的字段含义

列	数据类型	描述
SID	NUMBER	目前正在运行任务的会话ID
JOB	NUMBER	任务的唯一标示符
FAILURES	NUMBER	连续不成功执行的累计次数
LAST_DATE	DATE	最后一次成功执行的日期
LAST_SEC	VARCHAR2(8)	如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE	DATE	目前正在运行任务的开始日期
THIS_SEC	VARCHAR2(8)	如HH24:MM:SS格式的this_date日期的小时，分钟和秒

任务运行间隔指定

任务重复运行的时间间隔取决于interval参数中设置的日期表达式。

最简单的需求是特定的时间间隔后，重复运行该任务。例如天运行一次'sysdate+1'。

这样可能带来的问题是不能保证任务的下一次运行的准确时间。

比如第一次运行在午夜12点，这样以后每次运行理论上都在午夜12点。

但是如果某用户在上午8点使用run过程执行了该任务，以后该任务重新定时到上午8点运行。

另外如果数据库关闭或者说任务队列非常的忙以至于任务不能在计划的那个时间点准时执行，

都可能运行时间的不断“漂移”。

另一种需求是需要要任务在特定时间执行，下面是一些例子。

#每天午夜12点 'TRUNC(SYSDATE + 1)' 

#每天早上8点30分 'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)' 

#每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24' 

#每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)' 

#每季度的第一天凌晨1点 'TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24'

#每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'

#每年1月1日凌晨1点执行 'add_months(trunc(sysdate,''yyyy''),12)+1/24'

#每星期六和日上午11点 'trunc(least(next_day(sysdate,7),next_day(sysdate,1)))+(11*60)/(24*60)'

  注:1-星期天 2-星期1 3-星期2 ... 7-星期6