job每小时执行一次 oracle_ORACLE 定时任务job

DBMS_JOB系统包是Oracle“任务队列”子系统的API编程接口。DBMS_JOB包对于任务队列提供了下面这些功能：提交并且执行一个任务、改变任务的执行参数以及删除或者临时挂起任务等。

DBMS_JOB包是由ORACLE_HOME目录下的rdbms/admin子目录下的DBMSJOB.SQL和PRVTJOB.PLB 这两个脚本文件创建的。这两个文件被CATPROC.SQL脚本文件调用，而CATPROC.SQL这个文件一般是在数据库创建后立即执行的。脚本为DBMS_JOB包创建了一个公共同义词，并给该包授予了公共的可执行权限，所以所有的Oracle用户均可以使用这个包。

下面几个数据字典视图是关于任务队列信息的，主要有DBA_JOBS, USER_JOBS和DBA_JOBS_RUNNING。这些字典视图是由名为CATJOBQ.SQL的脚本文件创建的。该脚本文件和创建DBMS_JOB包的脚本文件一样在ORACLE_HOME目录的rdbms/admin子目录中，同样也是由脚本文件CATPROC.SQL调用。

最后，要使任务队列能正常运行，还必须启动它自己专有的后台过程。启动后台过程是通过在初始化文件init*.ora(实例不同，初始化文件名也略有不同)中设置初始化参数来进行的。下面就是该参数：

JOB_QUEUE_PROCESSES = n

其中，n可以是0到36之间的任何一个数。除了该参数以外，还有几个关于任务队列的初始化参数，本文后面将会对其进行详细讨论。

DBMS_JOB包中包含有许多过程，见表1所示。

表1 DBMS_JOB包

名称类型描述DBMS_JOB.ISUBMIT过程提交一个新任务，用户指定一个任务号DBMS_JOB.SUBMIT过程提交一个新任务，系统指定一个任务号DBMS_JOB.REMOVE过程从队列中删除一个已经存在的任务DBMS_JOB.CHANGE过程更改用户设定的任务参数DBMS_JOB.WHAT过程更改PL/SQL任务定义DBMS_JOB.NEXT_DATE过程更改任务下一次运行时间DBMS_JOB.INTERVAL过程更改任务运行的时间间隔DBMS_JOB.BROKEN过程将任务挂起，不让其重复运行DBMS_JOB.RUN过程在当前会话中立即执行任务DBMS_JOB.USER_EXPORT过程创建文字字符串，用于重新创建一个任务

三、DBMS_JOB包参数

DBMS_JOB包中所有的过程都有一组相同的公共参数，用于定义任务，任务的运行时间以及任务定时运行的时间间隔。这些公共任务定义参数见表2所示。

表2 DBMS_JOB过程的公共参数

名称类型注释JobBINARY_INTEGER任务的唯一识别号WhatVARCHAR2作为任务执行的PL/SQL代码Next_dateVARCHAR2任务下一次运行的时间IntervalVARCHAR2日期表达式，用来计算下一次任务运行的时间

下面我们来详细讨论这些参数的意义及用法。

1、job

参数job是一个整数，用来唯一地标示一个任务。该参数既可由用户指定也可由系统自动赋予，这完全取决于提交任务时选用了那一个任务提交过程。DBMS_JOB.SUBMIT过程通过获得序列SYS.JOBSEQ的下一个值来自动赋予一个任务号。该任务号是作为一个OUT参数返回的，所以调用者随后可以识别出提交的任务。而DBMS_JOB.ISUBMIT过程则由调用者给任务指定一个识别号，这时候，任务号的唯一性就完全取决于调用者了。

除了删除或者重新提交任务，一般来说任务号是不能改变的。即使当数据库被导出或者被导入这样极端的情况，任务号也将被保留下来。所以在执行含有任务的数据的导入/导出操作时很可能会发生任务号冲突的现象。

2、what

what参数是一个可以转化为合法PL/SQL调用的字符串，该调用将被任务队列自动执行。在what参数中，如果使用文字字符串，则该字符串必须用单引号括起来。 what参数也可以使用包含我们所需要字符串值的VARCHAR2变量。实际的PL/SQL调用必须用分号隔开。在PL/SQL调用中如果要嵌入文字字符串，则必须使用两个单引号。

what参数的长度在Oracle7.3中限制在2000个字节以内，在Oracle 8.0以后，扩大到了4000个字节，这对于一般的应用已完全足够。该参数的值一般情况下都是对一个PL/SQL存储过程的调用。在实际应用中，尽管可以使用大匿名Pl/SQL块，但建议大家最好不要这样使用。还有一个实际经验就是最好将存储过程调用封装在一个匿名块中，这样可以避免一些比较莫名错误的产生。我来举一个例子，一般情况下，what参数可以这样引用：

what =>’my_procedure(parameter1);’

但是比较安全的引用，应该这样写：

what =>’begin my_procedure(parameter1); end;’

任何时候，我们只要通过更改what参数就可以达到更改任务定义的目的。但是有一点需要注意，通过改变what参数来改变任务定义时，用户当前的会话设置也被记录下来并成为任务运行环境的一部分。如果当前会话设置和最初提交任务时的会话设置不同，就有可能改变任务的运行行为。意识到这个潜在的副作用是非常重要的，无论何时只要应用到任何DBMS_JOB过程中的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合法日期表达式的字符串。这个日期字符串的值在每次任务被执行时算出，算出的日期表达式有两种可能，要么是未来的一个时间要么就是null。这里要强调一点：很多开发者都没有意识到next_date是在一个任务开始时算出的，而不是在任务成功完成时算出的。

当任务成功完成时，系统通过更新任务队列目录表将前面算出的next_date值置为下一次任务要运行的时间。当由interval表达式算出next_date是null时，任务自动从任务队列中移出，不会再继续执行。因此，如果传递一个null值给interval参数，则该任务仅仅执行一次。

通过给interval参数赋各种不同的值，可以设计出复杂运行时间计划的任务。本文后面的“任务间隔和日期算法”将对interval表达式进行详细讨论，并给出一个实际有用interval表达式的例子。

四、任务队列架构和运行环境

任务队列在Oracle系统中其实是一个子系统，它具有自己特定的后台过程和目录表。该子系统设计的目的是为了能不在用户干预下自动运行PL/SQL过程。

1、任务队列后台过程

任务队列(SNP)后台过程随着Oracle实例的启动而同时启动。在文章前面已经谈到初始化文件init.ora中的参数JOB_QUEUE_PROCESSES，用来设置有几个队列过程。这里设置了几个过程，系统中就会有几个SNP过程被启动。JOB_QUEUE_PROCESSES这个参数，可以是0到36中的任何一个数，也就是说对于每个Oracle实例最多可以有36个SNP过程，也可以不支持队列过程(＝0)。在大多数操作系统中，SNP三个字母常作为过程名的一部分出现。如，在unix系统中，如果该Oracle实例名为ora8，有三个任务队列过程，则这三个任务队列过程名称为：

ora_ora8_snp0

ora_ora8_snp1

ora_ora8_snp2

SNP后台过程和其他的Oracle后台过程的一个重要区别就是杀掉一个SNP过程不会影响到Oracle实例。当一个任务队列过程失控或者消耗太多的资源时，就可以将其杀掉，当然这种情况不是经常遇到的。当一个SNP过程被杀掉或者失败时，Oracle就自动启动一个新的SNP过程来代替它。

2、有关任务队列的初始化参数

初始化文件init.ora中的几个参数控制着任务队列后台的运行，下面我们将对其进行详细讨论。

(1)、JOB_QUEUE_INTERVAL

任务队列过程定期唤醒并检查任务队列目录表是否有任务需要执行。参数JOB_QUEUE_INTERVAL决定SNP过程两次检查目录表之间“休眠”多长时间(单位为秒)。间隔设的太小会造成由于SNP过程不断检查目录表而导致不必要的系统吞吐量。相反如果间隔设得太大，SNP过程在特定的时间没有被唤醒，那个时间的任务就不会能被运行。最佳的时间间隔设置要综合考虑系统环境中不同的任务，60秒的确省设置可以满足大多数的应用。

(2)、JOB_QUEUE_KEEP_CONNECTIONS

除了前面介绍的JOB_QUEUE_PROCESS和JOB_QUEUE_INTERVAL两个参数以外，影响SNP后台过程行为的第三个参数是JOB_QUEUE_KEEP_CONNECTIONS。当该参数为TRUE时，SNP过程在两个任务的运行期间(也就是休眠期间)，仍然和Oracle保持开放的连接。相反，如果为FALSE时，SNP过程将和数据库断开连接，当唤醒时刻到来时又重新连接并检查任务队列。

选择这两种方法中的那一种，主要是考虑任务队列的有效性和数据库关闭方法。长期保持连接的效率比较高，但任务队列会受到正常关闭数据库的影响。这是因为任务队列过程对于服务器管理器看来和一个普通用户的过程没有什么不同，而正常的关闭数据库需要让所有的用户都断开连接。而断开连接和重新连接又给数据库增加了负荷，但是可定期地使数据库没有可连接SNP过程，也就可以使数据库正常关闭。对于有很多任务或者是任务重复执行的时间间隔较短(一个小时或者更少)的环境，一般将JOB_QUEUE_KEEP_CONNECTIOONS设置为TRUE，并修改关闭数据库的脚本为立即关闭。对于严格要求采用正常方式关闭的数据库或者是任务较少，重复间隔较长的环境，一般将该参数设置为FALSE。最好，要提醒一句，SNP过程仅在没有任何任务运行时才断开，这种情况下，那些需要比较长时间运行的任务SNP将在它们的生命周期内一致保持开放的连接，这就延迟了正常关闭数据库的时间。

3、建立运行环境

当SNP过程唤醒时，它首先查看任务队列目录中所有的任务是否当前的时间超过了下一次运行的日期时间。SNP检测到需要该时间立即执行的任务后，这些任务按照下一次执行日期的顺序依次执行。当SNP过程开始执行一个任务时，其过程如下：以任务所有者的用户名开始一个新的数据库会话。

当任务第一次提交或是最后一次被修改时，更改会话NLS设置和目前就绪的任务相匹配。

通过interval日期表达式和系统时间，计算下一次执行时间。

执行任务定义的PL/SQL

如果运行成功，任务的下一次执行日期(next_date)被更新，否则，失败计数加1。

经过JOB_QUEUS_INTERVAL秒后，又到了另一个任务的运行时间，重复上面的过程。

在前两步中，SNP过程创建了一个模仿用户运行任务定义的PL/SQL的会话环境。然而，这个模仿的运行环境并不是和用户实际会话环境完全一样，需要注意以下两点：第一，在任务提交时任何可用的非确省角色都将在任务运行环境中不可用。因此，那些想从非确省角色中取得权限的任务不能提交，用户确省角色的修改可以通过在任务未来运行期间动态修改来完成。第二，任何任务定义本身或者过程执行中需要的数据库联接都必须完全满足远程的用户名和密码。SNP过程不能在没有显式指明口令的情况下初始化一个远程会话。显然，SNP过程不能假定将本地用户的口令作为远程运行环境会话设置的一部分。

提交的任务如果运行失败会怎么样呢？当任务运行失败时，SNP过程在1分钟后将再次试图运行该任务。如果这次运行又失败了，下一次尝试将在2分钟后进行，再下一次在4分钟以后。任务队列每次加倍重试间隔直到它超过了正常的运行间隔。在连续16次失败后，任务就被标记为中断的(broken)，如果没有用户干预，任务队列将不再重复执行。

五、任务队列字典表和视图

任务队列中的任务信息可以通过表3所示的几个字典视图来查看，这些视图是由CATJOBQ.sql脚本创建的。表4和5是各个视图每个字段的含义。

表3. 任务队列中关于任务的数据字典视图

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

表4. DBA_JOBS 和 USER_JOBS.字典视图的字段含义

字段(列)类型描述JOBNUMBER任务的唯一标示号LOG_USERVARCHAR2(30)提交任务的用户PRIV_USERVARCHAR2(30)赋予任务权限的用户SCHEMA_USERVARCHAR2(30)对任务作语法分析的用户模式LAST_DATEDATE最后一次成功运行任务的时间LAST_SECVARCHAR2(8)如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATEDATE正在运行任务的开始时间，如果没有运行任务则为nullTHIS_SECVARCHAR2(8)如HH24:MM:SS格式的this_date日期的小时，分钟和秒NEXT_DATEDATE下一次定时运行任务的时间NEXT_SECVARCHAR2(8)如HH24:MM:SS格式的next_date日期的小时，分钟和秒TOTAL_TIMENUMBER该任务运行所需要的总时间，单位为秒BROKENVARCHAR2(1)标志参数，Y标示任务中断，以后不会运行INTERVALVARCHAR2(200)用于计算下一运行时间的表达式FAILURESNUMBER任务运行连续没有成功的次数WHATVARCHAR2(2000)执行任务的PL/SQL块CURRENT_SESSION_LABELRAW MLSLABEL该任务的信任Oracle会话符CLEARANCE_HIRAW MLSLABEL该任务可信任的Oracle最大间隙CLEARANCE_LORAW MLSLABEL该任务可信任的Oracle最小间隙NLS_ENVVARCHAR2(2000)任务运行的NLS会话设置MISC_ENVRAW(32)任务运行的其他一些会话参数

表 5. 视图DBA_JOBS_RUNNING的字段含义

列数据类型描述SIDNUMBER目前正在运行任务的会话IDJOBNUMBER任务的唯一标示符FAILURESNUMBER连续不成功执行的累计次数LAST_DATEDATE最后一次成功执行的日期LAST_SECVARCHAR2(8)如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATEDATE目前正在运行任务的开始日期THIS_SECVARCHAR2(8)如HH24:MM:SS格式的this_date日期的小时，分钟和秒

六、任务重复运行间隔和间隔设计算法

任务重复运行的时间间隔取决于interval参数中设置的日期表达式。下面就来详细谈谈该如何设置interval参数才能准确满足我们的任务需求。一般来讲，对于一个任务的定时执行，有三种定时要求。在一个特定的时间间隔后，重复运行该任务。

在特定的日期和时间运行任务。

任务成功完成后，下一次执行应该在一个特定的时间间隔之后。

第一种调度任务需求的日期算法比较简单，即'SYSDATE+n',这里n是一个以天为单位的时间间隔。表6给出了一些这种时间间隔设置的例子。

表6 一些简单的interval参数设置例子

描述Interval参数值每天运行一次'SYSDATE + 1'每小时运行一次'SYSDATE + 1/24'每10分钟运行一次'SYSDATE + 10/(60*24)'每30秒运行一次'SYSDATE + 30/(60*24*60)'每隔一星期运行一次'SYSDATE + 7'不再运行该任务并删除它NULL

表6所示的任务间隔表达式不能保证任务的下一次运行时间在一个特定的日期或者时间，仅仅能够指定一个任务两次运行之间的时间间隔。例如，如果一个任务第一次运行是在凌晨12点，interval指定为'SYSDATE + 1',则该任务将被计划在第二天的凌晨12点执行。但是，如果某用户在下午4点手工(DBMS_JOB.RUN)执行了该任务，那么该任务将被重新定时到第二天的下午4点。还有一个可能的原因是如果数据库关闭或者说任务队列非常的忙以至于任务不能在计划的那个时间点准时执行。在这种情况下，任务将试图尽快运行，也就是说只要数据库一打开或者是任务队列不忙就开始执行，但是这时，运行时间已经从原来的提交时间漂移到了后来真正的运行时间。这种下一次运行时间的不断“漂移”是采用简单时间间隔表达式的典型特征。

第二种调度任务需求相对于第一种就需要更复杂的时间间隔(interval)表达式，表7是一些要求在特定的时间运行任务的interval设置例子。

表 7. 定时到特定日期或时间的任务例子

描述INTERVAL参数值每天午夜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)'每个季度最后一天的晚上11点'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'每星期六和日早上6点10分'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60)'

第三种调度任务需求无论通过怎样设置interval日期表达式也不能满足要求。这时因为一个任务的下一次运行时间在任务开始时才计算，而在此时是不知道任务在何时结束的。遇到这种情况怎么办呢？当然办法肯定是有的，我们可以通过为任务队列写过程的办法来实现。这里我只是简单介绍以下，可以在前一个任务队列执行的过程中，取得任务完成的系统时间，然后加上指定的时间间隔，拿这个时间来控制下一个要执行的任务。这里有一个前提条件，就是目前运行的任务本身必须要严格遵守自己的时间计划。

结论

Oracle中的定时任务是在Oracle系统中是一个非常重要的子系统，运用得当，可以极大的提高我们的系统运行和维护能力。而Oracle数据复制的延迟事务队列管理完全是基于Oracle的队列任务，对其的深刻理解有助于我们更好地管理数据复制。

第二章：

oracle job有定时执行的功能，可以在指定的时间点或每天的某个时间点自行执行任务。

一、查询系统中的job，可以查询视图

--相关视图

select * from dba_jobs;

select * from all_jobs;

select * fromuser_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_LABELRAW MLSLABEL 该任务的信任Oracle会话符

CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙

CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙

NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置

MISC_ENV RAW(32) 任务运行的其他一些会话参数

*/

-- 正在运行job

select * fromdba_jobs_running;

其中最重要的字段就是job这个值就是我们操作job的id号，what 操作存储过程的名称，next_date 执行的时间，interval执行间隔

二、执行间隔interval运行频率

描述 INTERVAL参数值

每天午夜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)

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

每星期六和日早上6点10分 TRUNC(LEAST(NEXT_DAY(SYSDATE,''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60)

每秒钟执行次 Interval => sysdate+ 1/(24 * 60 * 60)

如果改成sysdate + 10/(24 *60 * 60)就是10秒钟执行次

每分钟执行

Interval =>TRUNC(sysdate,'mi') + 1/ (24*60)

如果改成TRUNC(sysdate,'mi')+ 10/ (24*60) 就是每10分钟执行次

每天定时执行

例如：每天的凌晨1点执行

Interval =>TRUNC(sysdate) + 1 +1/ (24)

每周定时执行

例如：每周一凌晨1点执行

Interval =>TRUNC(next_day(sysdate,'星期一'))+1/24

每月定时执行

例如：每月1日凌晨1点执行

Interval=>TRUNC(LAST_DAY(SYSDATE))+1+1/24

每季度定时执行

例如每季度的第一天凌晨1点执行

Interval =>TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

每半年定时执行

例如：每年7月1日和1月1日凌晨1点

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

每年定时执行

例如：每年1月1日凌晨1点执行

Interval=>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

三、创建job方法

创建job,基本语法：

declare

variable job number;

begin

sys.dbms_job.submit(job => :job,

what => 'prc_name;', --执行的存储过程的名字

next_date => to_date('22-11-201309:09:41', 'dd-mm-yyyy hh24:mi:ss'),

interval =>'sysdate+1/86400'); --每天86400秒钟，即一秒钟运行prc_name过程一次

commit;

end;

使用dbms_job.submit方法过程，这个过程有五个参数：job、what、next_date、interval与no_parse。

dbms_job.submit(

job OUT binary_ineger,

What IN varchar2,

next_date IN date,

interval IN varchar2,

no_parse IN booean:=FALSE)

job参数是输出参数，由submit()过程返回的binary_ineger，这个值用来唯一标识一个工作。一般定义一个变量接收，可以去user_jobs视图查询job值。

what参数是将被执行的PL/SQL代码块，存储过程名称等。

next_date参数指识何时将运行这个工作。

interval参数何时这个工作将被重执行。

no_parse参数指示此工作在提交时或执行时是否应进行语法分析——true，默认值false。指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。

四、其他job相关的存储过程

在dbms_job这个package中还有其他的过程：broken、change、interval、isubmit、next_date、remove、run、submit、user_export、what；

大致介绍下这些过程：

1、broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。这个过程有三个参数：job、broken与next_date。

procedure broken (

job IN binary_integer,

broken IN boolean,

next_date IN date := SYSDATE

)

job参数是工作号，它在问题中唯一标识工作。

broken参数指示此工作是否将标记为破——true说明此工作将标记为破，而false说明此工作将标记为未破。

next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。

job如果由于某种原因未能成功执行，oracle将重试16次后，还未能成功执行，将被标记为broken，重新启动状态为broken的job，有如下两种方式;

a、利用dbms_job.run()立即执行该job

begin

dbms_job.run(:job) --该job为submit过程提交时返回的jobnumber或是去dba_jobs去查找对应job的编号

end;

b、利用dbms_job.broken()重新将broken标记为false

begin

dbms_job.broken (:job, false, next_date)

end;

2、change()过程用来改变指定job的设置。

这个过程有四个参数：job、what、next_date、interval。

procedure change (

job IN binary_integer,

what IN varchar2,

next_date IN date,

interval IN varchar2

)

这里，

job参数是一个整数值，它唯一标识此工作。

what参数是由此job运行的一块PL/SQL代码块。

next_date参数指示何时此job将被执行。

interval参数指示一个job重执行的频度。

3、interval()过程用来显式地设置重复执行一个job之间的时间间隔数。

这个过程有两个参数：job、interval。

procedure interval(

job IN binary_integer,

interval IN varchar2

)

job参数标识一个特定的工作。

interval参数指示一个工作重执行的频度。

4、isubmit()过程用来用特定的job号提交一个job。

这个过程有五个参数：job、what、next_date、interval、no_parse。

procedure isubmit (

job IN binary_ineger,

what IN varchar2,

next_date IN date,

interval IN varchar2,

no_parse IN booean := FALSE

)

这个过程与submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的job号。如果提供的job号已被使用，将产生一个错误。

5、next_date()过程用来显式地设定一个job的执行时间。这个过程接收两个参数：job、next_date。

procedure next_date(

job IN binary_ineger,

next_date IN date

)

job标识一个已存在的工作。

next_date参数指示了此job应被执行的日期、时间。

6、remove()过程来删除一个已计划运行的job。这个过程接收一个参数：

procedure remove(job INbinary_ineger);

job参数唯一地标识一个工作这个参数的值是由为此工作调用submit()过程返回的job参数的值，已正在运行的job不能删除。

7、run()过程用来立即执行一个指定的job。这个过程只接收一个参数：

procedure run(job INbinary_ineger)

job参数标识将被立即执行的工作。

8、使用submit()过程，job被正常地计划。

9、user_export()过程返回一个命令，此命令用来安排一个存在的job以便此job能重新提交。此程序有两个参数：job、my_call。

procedure user_export(

job IN binary_ineger,

my_call IN OUT varchar2

)

job参数标识一个安排了的工作。

my_call参数包含在它的当前状态重新提交此job所需要的正文。

10、what()过程应许在job执行时重新设置此正在运行的命令。这个过程接收两个参数：job、what。

procedure what (

job IN binary_ineger,

what IN OUT varchar2

)

job参数标识一个存在的工作。

what参数指示将被执行的新的PL/SQL代码。实现的功能：每隔一分钟自动向getSysDate表中插入当前的系统时间。

五、示例

/* 每10秒钟执行一次 插入一条时间 */

-- 创建table

create table tab_time(

current_time timestamp

);

-- 创建存储过程

create or replace procedure pro_job_print

as

begin

--dbms_output.put_line('系统时间：' ||to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss'));

insert into tab_time values(sysdate);

end;

-- 调用过程测试

begin

pro_job_print;

end;

--select 24 * 60 * 60 from dual; --得出一天的秒数

-- 创建job

declare

job1 number;

begin

dbms_job.submit(job1, 'pro_job_print;',sysdate, 'sysdate+10/86400'); --每10插入一条记录

end;

--相关视图

select * from dba_jobs;

select * from all_jobs;

select * fromuser_jobs;

-- 正在运行job

select * fromdba_jobs_running;

-- 运行job

begin

dbms_job.run(26); --和select * from user_jobs;中的job值对应，看what对应的过程

end;

-- 查询是否插入数据

selectto_char(current_time, 'dd-mm-yyyy hh24:mi:ss') current_time from tab_time orderby current_time;

-- 删除一个job

begin

dbms_job.remove(26);--和select * fromuser_jobs; 中的job值对应，看what对应的过程

end;

六、关于设置job任务数量和控制并发

初始化相关参数job_queue_processes

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

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

job_queue_process表示oracle能够并发的job的数量，sqlplus中可以通过语句

show parameterjob_queue_process; 来查看oracle中job_queue_process的值。

select * fromv$parameter;

select name,description from v$bgprocess;

当job_queue_process值为0时表示全部停止oracle的job，可以通过语句 alter system setjob_queue_processes = 10; 来调整启动oracle的job。

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

七、job不运行的大概原因

(1)、上面讲解了job的参数：与job相关的参数一个是job_queue_processes，这个是运行job时候所起的进程数，当然系统里面job大于这个数值后，就会有排队等候的，最小值是0，表示不运行job，最大值是1000，在OS上对应的进程时SNPn，9i以后OS上管理job的进程叫CJQn。可以使用下面这个SQL确定目前有几个SNP/CJQ在运行。

select * fromv$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(, FALSE);

end;

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

select * fromdba_jobs_running;

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

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

alter system setjob_queue_processes = 0; --关闭job进程，等待5--10秒钟

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

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

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

begin

dbms_job.run(_ID)

end;

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

第三章

再有大量的表级操作时,建议用数据库本身的job queue,这样方便,效率高;如果用系统级定时,

会增加很多编程工作,成本增加了,还很容易出错,事情越简单出错的几率越小.

再使用job queue之前,我们还要简单配置下,oracle定时执行job queue 的后台进程是SNP,要启动

snp,首先看系统模式是否支持

sql> alter system enable restricted session;

或

sql> alter system disenable restricted session;

利用上面的命令更改系统的会话方式为disenable restricted,为snp的启动创建条件.

再有就是配置job queue的启动参数,snp的启动参数位于oracle的初始化文件中,

job_queue_processes=10 (oracle10gde 默认值)

job_queue_interval=N

第一行定义snp进程的启动个数为10,正常得女冠一范围是0-36,根据任务的多少,可以配置

不同的数值.

第二行定义系统每隔几秒唤醒该进程一次.缺省是60,正常范围是1-3600秒.事实上，该进程执行完

当前任务后，就进入睡眠状态，睡眠一段时间后，由系统的总控负责将其唤醒。

如果该文件中没有上面两行，请按照如上配置添加。配置完成后，需要重新启动数据库，使其生效

。注意：如果任务要求执行的间隔很短的话，N的配置也要相应地小一点。

查看job queue的详细信息,查询数据库字典 user_jobs

eg:

sql> select job,next_date,next_sec,broken from user_jobs;

包含以下子过程：

Broken()过程。

change()过程。

Interval()过程。

Isubmit()过程。

Next_Date()过程。

Remove()过程。

Run()过程。

Submit()过程。

User_Export()过程。

What()过程。

1、

Broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。

这个过程有三个参数：job 、broken与next_date。

PROCEDURE Broken (job IN binary_integer,

Broken IN boolean,

next_date IN date :=SYSDATE)

job参数是工作号，它在问题中唯一标识工作。

broken参数指示此工作是否将标记为破——TRUE说明此工作将标记为破，而FLASE说明此工作将标记为未破。

next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。

2、

Change()过程用来改变指定工作的设置。

这个过程有四个参数：job、what 、next_date与interval。

PROCEDURE Change (job IN binary_integer,

What IN varchar2,

next_date IN date,

interval IN varchar2)

此job参数是一个整数值，它唯一标识此工作。

What参数是由此工作运行的一块PL/SQL代码块。

next_date参数指示何时此工作将被执行。

interval参数指示一个工作重执行的频度。

3、

Interval()过程用来显式地设置重执行一个工作之间的时间间隔数。

这个过程有两个参数：job与interval。

PROCEDURE Interval (job IN binary_integer,

Interval IN varchar2)

job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。

4、

ISubmit()过程用来用特定的工作号提交一个工作。

这个过程有五个参数：job、what、next_date、interval与no_parse。

PROCEDURE ISubmit (job IN binary_ineger,

What IN varchar2,

next_date IN date,

interval IN varchar2,

no_parse IN booean:=FALSE)

这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个

由开发者提供的工作号。如果提供的工作号已被使用，将产生一个错误。

5、

Next_Date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job与next_date。

PROCEDURE Next_Date(job IN binary_ineger,

next_date IN date)

job标识一个已存在的工作。next_date参数指示了此工作应被执行的日期与时间。

6、

Remove()过程来删除一个已计划运行的工作。这个过程接收一个参数：

PROCEDURE Remove(job IN binary_ineger);

job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。

已正在运行的工作不能由调用过程序删除。

7、

Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：

PROCEDURE Run(job IN binary_ineger)

job参数标识将被立即执行的工作。

8、

使用Submit()过程，工作被正常地计划好。

这个过程有五个参数：job、what、next_date、interval与no_parse。

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代码应立即进行语法分析。

9、

User_Export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。

此程序有两个参数：job与my_call。

PROCEDURE User_Export(job IN binary_ineger,

my_call IN OUT varchar2)

job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要

的正文。

10、

What()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job与what。

PROCEDURE What (job IN binary_ineger,

What IN OUT varchar2)

job参数标识一个存在的工作。what参数指示将被执行的新的PL/SQL代码。

一个简单例子：

创建测试表

SQL> create table a(a date);

表已创建。

创建一个自定义过程

SQL> create or replace procedure test as

2 begin

3 insert into a values(sysdate);

4 end;

5 /

过程已创建。

创建JOB

SQL> variable job1 number;

SQL>

SQL> begin

2 dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440');　　--每天1440分钟，即一分钟运行test过程一次

3 end;

4 /

PL/SQL 过程已成功完成。

运行JOB

SQL> begin

2 dbms_job.run(:job1);

3 end;

4 /

PL/SQL 过程已成功完成。

SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;

时间

-------------------

2001/01/07 23:51:21

2001/01/07 23:52:22

2001/01/07 23:53:24

删除JOB

SQL> begin

2 dbms_job.remove(:job1);

3 end;

4 /

PL/SQL 过程已成功完成。

下面说下常用的视图与参数：

任务队列中关于任务的数据字典视图

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

JOB_QUEUE_PROCESSES >= 1 (如果系统在同一时间会运行很多的job, 或者还有大量需要自动refresh的snapshot, 适当加大)

JOB_QUEUE_INTERVAL : 秒数(缺省为60秒), 根据你的job的调度频度而定, 对于一般的一天运行一次的job, 设为缺省值或者几分钟都可以. (不要设置过小, 以免影响性能)

JOB_QUEUE_KEEP_CONNECTION (系统默认就可以，我没发现他的作用)

DBA_JOBS 和 USER_JOBS.字典视图的字段含义

字段(列)类型描述JOBNUMBER任务的唯一标示号LOG_USERVARCHAR2(30)提交任务的用户PRIV_USERVARCHAR2(30)赋予任务权限的用户SCHEMA_USERVARCHAR2(30)对任务作语法分析的用户模式LAST_DATEDATE最后一次成功运行任务的时间LAST_SECVARCHAR2(8)如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATEDATE正在运行任务的开始时间，如果没有运行任务则为nullTHIS_SECVARCHAR2(8)如HH24:MM:SS格式的this_date日期的小时，分钟和秒NEXT_DATEDATE下一次定时运行任务的时间NEXT_SECVARCHAR2(8)如HH24:MM:SS格式的next_date日期的小时，分钟和秒TOTAL_TIMENUMBER该任务运行所需要的总时间，单位为秒BROKENVARCHAR2(1)标志参数，Y标示任务中断，以后不会运行INTERVALVARCHAR2(200)用于计算下一运行时间的表达式FAILURESNUMBER任务运行连续没有成功的次数WHATVARCHAR2(2000)执行任务的PL/SQL块CURRENT_SESSION_LABELRAW MLSLABEL该任务的信任Oracle会话符CLEARANCE_HIRAW MLSLABEL该任务可信任的Oracle最大间隙CLEARANCE_LORAW MLSLABEL该任务可信任的Oracle最小间隙NLS_ENVVARCHAR2(2000)任务运行的NLS会话设置MISC_ENVRAW(32)任务运行的其他一些会话参数

视图DBA_JOBS_RUNNING的字段含义

列数据类型描述SIDNUMBER目前正在运行任务的会话IDJOBNUMBER任务的唯一标示符FAILURESNUMBER连续不成功执行的累计次数LAST_DATEDATE最后一次成功执行的日期LAST_SECVARCHAR2(8)如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATEDATE目前正在运行任务的开始日期THIS_SECVARCHAR2(8)如HH24:MM:SS格式的this_date日期的小时，分钟和秒

任务重复运行间隔和间隔设计算法任务重复运行的时间间隔取决于interval参数中设置的日期表达式。下面就来详细谈谈该如何设置interval参数才能准确满足我们的任务需求。一般来讲，对于一个任务的定时执行，有三种定时要求。

在一个特定的时间间隔后，重复运行该任务。

在特定的日期和时间运行任务。

任务成功完成后，下一次执行应该在一个特定的时间间隔之后。

第一种调度任务需求的日期算法比较简单，即'SYSDATE+n'，这里n是一个以天为单位的时间间隔。表6给出了一些这种时间间隔设置的例子。

表6 一些简单的interval参数设置例子

描述Interval参数值每天运行一次'SYSDATE + 1'每小时运行一次'SYSDATE + 1/24'每10分钟运行一次'SYSDATE + 10/(60*24)'每30秒运行一次'SYSDATE + 30/(60*24*60)'每隔一星期运行一次'SYSDATE + 7'不再运行该任务并删除它NULL

表6所示的任务间隔表达式不能保证任务的下一次运行时间在一个特定的日期或者时间，仅仅能够指定一个任务两次运行之间的时间间隔。例如，如果一个任务第一次运行是在凌晨12点，interval指定为'SYSDATE + 1'，则该任务将被计划在第二天的凌晨12点执行。但是，如果某用户在下午4点手工(DBMS_JOB.RUN)执行了该任务，那么该任务将被重新定时到第二天的下午4点。还有一个可能的原因是如果数据库关闭或者说任务队列非常的忙以至于任务不能在计划的那个时间点准时执行。在这种情况下，任务将试图尽快运行，也就是说只要数据库一打开或者是任务队列不忙就开始执行，但是这时，运行时间已经从原来的提交时间漂移到了后来真正的运行时间。这种下一次运行时间的不断“漂移”是采用简单时间间隔表达式的典型特征。

第二种调度任务需求相对于第一种就需要更复杂的时间间隔(interval)表达式，表7是一些要求在特定的时间运行任务的interval设置例子。

表 7. 定时到特定日期或时间的任务例子

描述INTERVAL参数值每天午夜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)'每个季度最后一天的晚上11点'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'每星期六和日早上6点10分'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60)'

第三种调度任务需求无论通过怎样设置interval日期表达式也不能满足要求。这时因为一个任务的下一次运行时间在任务开始时才计算，而在此时是不知道任务在何时结束的。遇到这种情况怎么办呢？当然办法肯定是有的，我们可以通过为任务队列写过程的办法来实现。这里我只是简单介绍以下，可以在前一个任务队列执行的过程中，取得任务完成的系统时间，然后加上指定的时间间隔，拿这个时间来控制下一个要执行的任务。这里有一个前提条件，就是目前运行的任务本身必须要严格遵守自己的时间计划。