使用DBMS_JOB包创建ORACLE定时任务

使用DBMS_JOB包创建ORACLE定时任务
2011-08-11 14:43 213人阅读 评论(0)收藏 举报
在Oracle的包里面，有一个名字叫做DBMS_JOB的包，它的作用是安排和管理作业队列。通过作业队列，可以让Oracle数据库定期执行特定的任务。当使用DBMS_JOB管理作业的时候，必须确保设置了初始化参数JOB_QUEUE_PROCESSES（不能为0）。　　
　　1、 SUBMIT
　　该过程用于建立一个新的作业，当建立作业的时候，需要通过设置相应的参数来告诉Oracle要执行的内容，要执行的时间，要执行任务的间隔。如下格式：
　　DBMS_JOB.SUBMIT(
　　 JOB OUT BINARY_INTERGER,
　　 WHATIN VARCHAR2，
　　 NEXT_DATE IN DATE DEFAULT SYSDATE,
　　 INTERVAL IN VARCHAR2 DEFAULT ‘NULL’,
　　 NO_PARSEIN BOOLEAN DEFAULT FALSE,
　　 INSTANCEIN BINARY_INTEGER DEFAULT ANY_INSTANCE,
　　 FORCEIN BOOLEAN DEFAULT FALSE
　　);
　　参数说明：

编号	参数	参数说明
1	job	用于指定作业编号
2	what	用于指定作业要执行的操作
3	next_date	用于指定该操作的下一次运行的日期
4	interval	用于指定该操作的时间间隔
5	no_parse	用于指定是否需要解析与作业相关的过程
6	instance	用于指定哪个例程可以运行作业？
7	force	用于指定是否强制运行与作业相关的例程

　　建立Oracle作业的例子：
　　DECLARE
　　JOBNO NUMBER; --通过查看该变量可以得到返回的作业编号
　　BEGIN
　　DBMS_JOB.SUBMIT(
　　 JOBNO,
　　 'PRC_SENDTOGX;', --执行工信局提供数据的脚本程序
　　 SYSDATE, --现在执行
　　 'SYSDATE+1'
　　);
　　END;
　　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'
每分钟执行一次	'SYSDATE+1/1440'

　　
　　2、 REMOVE
　　这个过程的作用是用于删除作业队列当中的特定的作业，它的语法如下：
　　DBMS_JOB.REMOVE(JOB IN BINARY_INTEGER);
　　下面是一个删除作业的例子：
　　首先查看DBA_JOBS表，看表里面有哪些任务正在执行着？
　　SELECT *　FROM DBA_JOBS;
　　可以看到里面的JOB就是我们要删除的作业的编号,LOG_USER是创建该任务的人。
　　SQL> EXEC DBMS_JOB.REMOVE(467);
　　SQL>COMMIT;
　　这样就能把已经建立的作业删除了。
　　3、 CHANGE
　　该过程改变与作业相关的所有的信息，其中包括作业的操作内容，作业运行的时间以及运行时间间隔信息等等。语法如下：
　　DBMS_JOB.CHANGE(
　　JOB IN BINARY_INTEGER,
　　WHAT IN VARCHAR2，
　　NEXT_DATE,
　　INTERVAL IN VARCHAR2，
　　INSTANCE IN BINARY_INTEGER DEFAULT NULL,
　　FORCE IN BOOLEAN DEFAULT FALSE
　　);
　　例子：
　　SQL>EXEC DBMS_JOB.CHANGE(2,NULL,NULL,’SYSDATE+2’);
　　SQL>COMMIT;
　　4、 WHAT
　　WHAT用来改变作业要执行的操作，例如：
　　SQL>EXEC DBMS_JOB.WHAT(268，’GETGX_AC01;’);
　　5、 NEXT_DATE
　　用来改变作业的下次运行日期
　　SQL>EXEC DBMS_JOB.NEXT_DATE(‘478’,’SYSDATE+2’);
　　6、 INTERVAL
　　该过程用来改变作业的运行时间间隔,下面的运行时间间隔修改为每分钟执行一次:
　　SQL>exec dbms_job.interval(478,’SYSDATE+1/24/60
　　7、 BROKEN
　　该过程用于给该作业打上中断标志,可以在DBA_JOBS表里面观察该作业的BROKEN标志知否为中断。例子：
　　SQL>EXEC DBMS_JOB.BROKEN(478，TRUE);
　　SQL>COMMIT;
　　8、 RUN
　　该过程用来执行该作业，例子：
　　SQL>EXEC DBMS_RN(478);
　　SQL>COMMIT;
　　
　　常见问题:
　　1、 如何停止一个作业？
　　SQL>DBMS_JOBS.BROKEN(2，TRUE);
　　SQL>COMMIT;
　　这里务必要提交哈!
　　2、 如何启动一个作业？
　　SQL>DBMS_JOBS.BROKEN(2,FALSE);
　　SQL>COMMIT;
　　这里也务必要提交,否则就没有效果
　　3、 前面提到的JOB_QUEUE_PROCESSES在什么地方设置？
　　ALTER SYSTEM SET job_queue_processes=39 SCOPE=SPFILE;
　　这个SQL的执行是需要具备相应的权限的。
　　4、 如何计算一个过程运行的时间（DATE和TimeStamp）？
　　可以在过程的开始设置一个时间，然后在过程的结尾处设置一个时间，然后两个时间的时间差可以计算出该过程运行的时间。通过实践发现使用SYSESTAMP来计算时间比较准确一些：
　　SELECT to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff4') FROM dual;


在Oracle的包里面，有一个名字叫做DBMS_JOB的包，它的作用是安排和管理作业队列。通过作业队列，可以让Oracle数据库定期执行特定的任务。当使用DBMS_JOB管理作业的时候，必须确保设置了初始化参数JOB_QUEUE_PROCESSES（不能为0）。　　
　　1、 SUBMIT
　　该过程用于建立一个新的作业，当建立作业的时候，需要通过设置相应的参数来告诉Oracle要执行的内容，要执行的时间，要执行任务的间隔。如下格式：
　　DBMS_JOB.SUBMIT(
　　 JOB OUT BINARY_INTERGER,
　　 WHATIN VARCHAR2，
　　 NEXT_DATE IN DATE DEFAULT SYSDATE,
　　 INTERVAL IN VARCHAR2 DEFAULT ‘NULL’,
　　 NO_PARSEIN BOOLEAN DEFAULT FALSE,
　　 INSTANCEIN BINARY_INTEGER DEFAULT ANY_INSTANCE,
　　 FORCEIN BOOLEAN DEFAULT FALSE
　　);
　　参数说明：

编号	参数	参数说明
1	job	用于指定作业编号
2	what	用于指定作业要执行的操作
3	next_date	用于指定该操作的下一次运行的日期
4	interval	用于指定该操作的时间间隔
5	no_parse	用于指定是否需要解析与作业相关的过程
6	instance	用于指定哪个例程可以运行作业？
7	force	用于指定是否强制运行与作业相关的例程

　　建立Oracle作业的例子：
　　DECLARE
　　JOBNO NUMBER; --通过查看该变量可以得到返回的作业编号
　　BEGIN
　　DBMS_JOB.SUBMIT(
　　 JOBNO,
　　 'PRC_SENDTOGX;', --执行工信局提供数据的脚本程序
　　 SYSDATE, --现在执行
　　 'SYSDATE+1'
　　);
　　END;
　　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'
每分钟执行一次	'SYSDATE+1/1440'

　　
　　2、 REMOVE
　　这个过程的作用是用于删除作业队列当中的特定的作业，它的语法如下：
　　DBMS_JOB.REMOVE(JOB IN BINARY_INTEGER);
　　下面是一个删除作业的例子：
　　首先查看DBA_JOBS表，看表里面有哪些任务正在执行着？
　　SELECT *　FROM DBA_JOBS;
　　可以看到里面的JOB就是我们要删除的作业的编号,LOG_USER是创建该任务的人。
　　SQL> EXEC DBMS_JOB.REMOVE(467);
　　SQL>COMMIT;
　　这样就能把已经建立的作业删除了。
　　3、 CHANGE
　　该过程改变与作业相关的所有的信息，其中包括作业的操作内容，作业运行的时间以及运行时间间隔信息等等。语法如下：
　　DBMS_JOB.CHANGE(
　　JOB IN BINARY_INTEGER,
　　WHAT IN VARCHAR2，
　　NEXT_DATE,
　　INTERVAL IN VARCHAR2，
　　INSTANCE IN BINARY_INTEGER DEFAULT NULL,
　　FORCE IN BOOLEAN DEFAULT FALSE
　　);
　　例子：
　　SQL>EXEC DBMS_JOB.CHANGE(2,NULL,NULL,’SYSDATE+2’);
　　SQL>COMMIT;
　　4、 WHAT
　　WHAT用来改变作业要执行的操作，例如：
　　SQL>EXEC DBMS_JOB.WHAT(268，’GETGX_AC01;’);
　　5、 NEXT_DATE
　　用来改变作业的下次运行日期
　　SQL>EXEC DBMS_JOB.NEXT_DATE(‘478’,’SYSDATE+2’);
　　6、 INTERVAL
　　该过程用来改变作业的运行时间间隔,下面的运行时间间隔修改为每分钟执行一次:
　　SQL>exec dbms_job.interval(478,’SYSDATE+1/24/60
　　7、 BROKEN
　　该过程用于给该作业打上中断标志,可以在DBA_JOBS表里面观察该作业的BROKEN标志知否为中断。例子：
　　SQL>EXEC DBMS_JOB.BROKEN(478，TRUE);
　　SQL>COMMIT;
　　8、 RUN
　　该过程用来执行该作业，例子：
　　SQL>EXEC DBMS_RN(478);
　　SQL>COMMIT;
　　
　　常见问题:
　　1、 如何停止一个作业？
　　SQL>DBMS_JOBS.BROKEN(2，TRUE);
　　SQL>COMMIT;
　　这里务必要提交哈!
　　2、 如何启动一个作业？
　　SQL>DBMS_JOBS.BROKEN(2,FALSE);
　　SQL>COMMIT;
　　这里也务必要提交,否则就没有效果
　　3、 前面提到的JOB_QUEUE_PROCESSES在什么地方设置？
　　ALTER SYSTEM SET job_queue_processes=39 SCOPE=SPFILE;
　　这个SQL的执行是需要具备相应的权限的。
　　4、 如何计算一个过程运行的时间（DATE和TimeStamp）？
　　可以在过程的开始设置一个时间，然后在过程的结尾处设置一个时间，然后两个时间的时间差可以计算出该过程运行的时间。通过实践发现使用SYSESTAMP来计算时间比较准确一些：
　　SELECT to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff4') FROM dual;