oracle job
一点一点学习jobs的各个方面比较长，比较烦，但是应该看完后会对jobs比较好的应用
一、学习准备
开始dbms_job学习前，先认识一个参数job_queue_processes
a、job_queue_processes参数决定了job作业能够使用的总进程数。
b、当该参数为0值，任何job都不会被执行，建议合理设置该值且至少大于1。
c、对于job运行时间也应该尽量合理的设置间隔以及启动时间。
d、如果同一时间内运行的Job数很多，过小的参数值导致job不得不进行等待。而过大的参数值则消耗更多的系统资源。
f、对于存在依赖关系的job，尽可能将其进行合并到一个job中，如使用chain等。
想细致了解的请前往
http://blog.csdn.net/robinson_0612/article/details/8694772
在使用dbms_jobs时首先确认job_queue_processes的值（10g系统默认为10）
SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 10
另外10g开始提供了一个新的job管理包：DBMS_SCHEDULER
http://space.itpub.net/8858072/viewspace-425547
http://www.cnblogs.com/lanzi/archive/2012/11/23/2784815.html
二、创建第一个job
SQL> create table a(a date); Table created. SQL> create or replace procedure job_test as 2 begin 3 insert into a values(sysdate); 4 end; 5 / Procedure created. SQL> exec job_test; PL/SQL procedure successfully completed. SQL> select * from a; A ------------------- 2013-05-31 15:45:32
创建一个job任务，时间间隔为每秒一次
SQL>variable job1 number;--注意别漏了定义变量 SQL> begin 2 dbms_job.submit(:job1,'job_test;',sysdate,'sysdate+1/64800'); 3 commit; 4 end; 5 / PL/SQL procedure successfully completed
也可以更清晰的使用这个方式建立job
variable job1 number; begin sys.dbms_job.submit(job => :job1, what => 'job_test;', next_date => sysdate, interval => 'sysdate+1/64800');--每天64800秒，即每秒执行一次，但是实际oracle的jobs不能精确到秒，这里只是为了快速测试效果 commit; end;
SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 2013-05-31 15:49:24 job_test; sysdate+1/64800 N SQL> select count(*) from a; COUNT(*) ---------- 9 SQL> / COUNT(*) ---------- 10 SQL> / COUNT(*) ---------- 10 SQL> select * from a; A ------------------- 2013-05-31 15:45:32 2013-05-31 15:48:58 2013-05-31 15:49:03 2013-05-31 15:49:08 2013-05-31 15:49:13 2013-05-31 15:49:18 2013-05-31 15:49:23 2013-05-31 15:49:28 2013-05-31 15:49:33 2013-05-31 15:49:38 2013-05-31 15:49:43 11 rows selected.
从实际执行结果看，秒级的任务并非按每秒执行，间隔在2-5秒间，原因位置，平时秒级的任务需求也没有，不做深入研究
三、修改job执行间隔
SQL> exec dbms_job.change(4,null,null,'sysdate+1/1440'); PL/SQL procedure successfully completed. SQL> commit; --一定要记得commit否则重启后会丢失 Commit complete. SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 2013-05-31 15:57:08 job_test; sysdate+1/1440 N SQL> truncate table a; Table truncated. SQL> select * from a; no rows selected SQL> select * from a; A ------------------- 2013-05-31 15:57:08 SQL> / A ------------------- 2013-05-31 15:57:08 2013-05-31 15:58:08 2013-05-31 15:59:08 2013-05-31 16:00:08 2013-05-31 16:01:08
修改成功
但是有瑕疵，08秒，假如我想精确，可以通过oracle的trunc函数(最后实验结果告诉我trunc可以输出00秒，但是jobs不能精确到秒)
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2013-05-31 16:02:25 SQL> select trunc(sysdate,'mi') from dual; TRUNC(SYSDATE,'MI') ------------------- 2013-05-31 16:02:00
再次修改job
SQL> exec dbms_job.change(4,null,null,'trunc(sysdate,''mi'')+1/1440'); PL/SQL procedure successfully completed. SQL> commit; SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 2013-05-31 16:12:00 job_test; trunc(sysdate,'mi')+ N 1/1440 SQL> select * from a; ..... A ------------------- 2013-05-31 16:08:09 2013-05-31 16:09:09 2013-05-31 16:10:09 2013-05-31 16:11:09 2013-05-31 16:12:04 2013-05-31 16:13:04 2013-05-31 16:14:04 2013-05-31 16:15:04 2013-05-31 16:16:04 2013-05-31 16:17:04 2013-05-31 16:18:04
还是不完美，继续证实精确到秒的job无法实现--至少我测试结果如此
关于trunc的详细使用，可以参考后面第六小节
四、临时停止jobs
SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 2013-05-31 16:12:00 job_test; trunc(sysdate,'mi')+ N 1/1440 SQL> exec dbms_job.broken(4,true); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 4000-01-01 00:00:00 job_test; trunc(sysdate,'mi')+ Y 1/1440
重新开始job
SQL> exec dbms_job.broken(4,false); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 2013-05-31 16:36:24 job_test; trunc(sysdate,'mi')+ N 1/1440
五、删除jobs
SQL> exec dbms_job.remove(4); PL/SQL procedure successfully completed. SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; no rows selected SQL> rollback;--不commit咱rollback看看 Rollback complete. SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; JOB N_DATE WHAT INTERVAL B ---------- ------------------- ---------- -------------------- - 4 2013-05-31 16:36:24 job_test; trunc(sysdate,'mi')+ N 1/1440 --jobs又回来了 SQL> exec dbms_job.remove(4); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs; no rows selected --commit后就真的没了
误删除后可以恢复所以记得正常删除需要commit
六、jobs的间隔设置之日期函数学习
如果jobs跟linux crontab那样 * * * * * 会觉得很时间设置很清晰
oracle感觉烦点
6.1 trunc()
先认识一个函数trunc()
这里只讨论trunc的date相关
语法：
TRUNC (datetime_exp, fmt)
其中：
date 一个表达式,该表达式标识一个日期和时间数。
fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
以下是一些常用的日期格式范例
1)SQL> select trunc(sysdate) from dual ; --返回当前日期，精确到凌晨0点0分 TRUNC(SYSDATE) ------------------- 2013-06-04 00:00:00 2)SQL> select trunc(sysdate, 'mm') from dual;--mm格式为返回当月第一天 TRUNC(SYSDATE,'MM') ------------------- 2013-06-01 00:00:00 3)SQL> select trunc(sysdate,'yy') from dual;--yy格式，返回当年第一天 TRUNC(SYSDATE,'YY') ------------------- 2013-01-01 00:00:00 4)SQL> select trunc(sysdate,'dd') from dual; --dd返回当前年月日，与trunc(sysdate)等效 TRUNC(SYSDATE,'DD') ------------------- 2013-06-04 00:00:00 5)SQL> select trunc(sysdate,'yyyy') from dual; --yy跟yy一样，返回当年第一天 TRUNC(SYSDATE,'YYYY ------------------- 2013-01-01 00:00:00 6)SQL> select trunc(sysdate,'d') from dual; --d返回本周第一天，按老美的，第一天是周日 TRUNC(SYSDATE,'D') ------------------- 2013-06-02 00:00:00 7)SQL> select trunc(sysdate, 'hh') from dual ; --hh返回当前时间，精确到 TRUNC(SYSDATE,'HH') ------------------- 2013-06-04 16:00:00 8)SQL> select trunc(sysdate, 'mi') from dual; --mi返回当前时间精确到分钟，TRUNC()函数没有秒的精确 TRUNC(SYSDATE,'MI') ------------------- 2013-06-04 16:29:00
再来几个函数
last_day,next_day,add_months
6.2 last_day
返回指定时间当月的最后一天
SQL> select last_day('2013-10-5') from dual; LAST_DAY('2013-10-5 ------------------- 2013-10-31 00:00:00 SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDATE) ------------------- 2013-06-30 18:16:30 想要下个月的3号 SQL> select last_day(sysdate)+3 from dual; LAST_DAY(SYSDATE)+3 ------------------- 2013-07-03 18:17:26 再组合下trunc SQL> select trunc(last_day(sysdate)+3) from dual; TRUNC(LAST_DAY(SYSD ------------------- 2013-07-03 00:00:00 再组合一个小时分钟 SQL> select trunc(last_day(sysdate)+3)+3/24+30/1440 from dual; TRUNC(LAST_DAY(SYSD ------------------- 2013-07-03 03:30:00
n/24表示小时
n/1440表示分钟
n/64800表示秒
6.3 next_day
返回下一个星期某一天
next_day(date,n)
想要每周3早上3点
SQL> select next_day(sysdate,4) from dual;--按老美的每周第一天是周日，所以周三是4 NEXT_DAY(SYSDATE,4) ------------------- 2013-06-05 18:22:59
组合下trunc、小时、分钟
SQL> select trunc(next_day(sysdate,4))+3/24+30/1440 from dual; TRUNC(NEXT_DAY(SYSD ------------------- 2013-06-05 03:30:00
6.4 add_months
返回指定日期后的多少个月
add_months(date,n)
以当前日期为起始，每2个月后的今天的3：30
SQL> select trunc(add_months(sysdate,2))+3/24+30/1440 from dual; TRUNC(ADD_MONTHS(SY ------------------- 2013-08-04 03:30:00 SQL> select trunc(add_months(trunc(sysdate,'yy'),2))+3/24+30/1440 from dual; TRUNC(ADD_MONTHS(TR ------------------- 2013-03-01 03:30:00
也可以简单点具体到每2个月那一天
SQL> select trunc(add_months('2013-01-03',2))+3/24+30/1440 from dual; TRUNC(ADD_MONTHS('2 ------------------- 2013-03-03 03:30:00
有了上面的基础后我们来制定一些计划任务
6.5 来个需求试试
每月3号10:00执行job_test任务
SQL> variable job1 number; SQL> begin 2 dbms_job.submit(:job1,'job_test;',sysdate,'trunc(last_day(sysdate)+3)+3/24+30/1440'); 3 commit; 4 end; 5 / PL/SQL procedure successfully completed. SQL> set linesize 200 SQL> col job format 999 SQL> col what format a50 SQL> col interval format a50 SQL> col next_date format a50 SQL> select job,what,interval,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') from user_jobs; JOB WHAT INTERVAL TO_CHAR(NEXT_DATE,' ---- -------------------------------------------------- -------------------------------------------------- ------------------- 8 job_test; trunc(last_day(sysdate)+3)+3/24+30/1440 2013-07-03 03:30:00
每周3凌晨3:30执行 job_test
SQL> variable job1 number; SQL> begin 2 dbms_job.submit(:job1,'job_test;',sysdate,'trunc(next_day(sysdate,4))+3/24+30/1440'); 3 commit; end; 4 5 / PL/SQL procedure successfully completed. SQL> SQL> select job,what,interval,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') from user_jobs; JOB WHAT INTERVAL TO_CHAR(NEXT_DATE,' ---- -------------------------------------------------- -------------------------------------------------- ------------------- 8 job_test; trunc(last_day(sysdate)+3)+3/24+30/1440 2013-07-03 03:30:00 9 job_test; trunc(next_day(sysdate,4))+3/24+30/1440 2013-06-05 03:30:00
每天3点
SQL> variable job1 number; SQL> begin 2 dbms_job.submit(:job1,'job_test;',sysdate,'trunc(sysdate+1)+3/24'); 3 commit; 4 end; 5 / PL/SQL procedure successfully completed. SQL> select job,what,interval,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') from user_jobs; JOB WHAT INTERVAL TO_CHAR(NEXT_DATE,' ---- -------------------------------------------------- -------------------------------------------------- ------------------- 10 job_test; trunc(sysdate+1)+3/24 2013-06-05 03:00:00 8 job_test; trunc(last_day(sysdate)+3)+3/24+30/1440 2013-07-03 03:30:00 9 job_test; trunc(next_day(sysdate,4))+3/24+30/1440 2013-06-05 03:30:00
一朋友还告诉我了一种拼接的方法，有兴趣的可以看看
每月3号早上10：00
variable job1 number; begin dbms_job.submit(:job1,'job_test;',sysdate,'to_date(to_char(add_months(sysdate,1),''yyyymm'')||''03 10:00:00'',''yyyy-mm-dd hh24:mi:ss'')'); commit; end; /
其他的...
每小时
dbms_job.submit(:job1,'job_test;',sysdate,'sysdate+1/24');
每十分钟
dbms_job.submit(:job1,'job_test;',sysdate,'sysdate+10/1440');
最后到指定某个特定时间点
variable job1 number; begin dbms_job.submit(:job1,'job_test;',to_date('2013-06-04 20:00:00','YYYY-MM-DD HH24:MI:SS'),''); commit; end; / SQL> select job,what,interval,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') from user_jobs; JOB WHAT INTERVAL TO_CHAR(NEXT_DATE,' ---- -------------------------------------------------- -------------------------------------------------- ------------------- 12 job_test; null 2013-06-04 20:00:00
在这里绕了点弯路，脑袋一直往前面习惯性的intervel设置
其实只执行一次next_date就妥妥的 -_-
七、dbms_job的具体语法
先以dbms_job.change为例
DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER, --job号 WHAT IN VARCHAR2， --执行什么 NEXT_DATE, --下一次执行时间 INTERVAL IN VARCHAR2， --执行间隔 INSTANCE IN BINARY_INTEGER DEFAULT NULL, FORCE IN BOOLEAN DEFAULT FALSE );
所以
exec dbms_job.change(4,null,null,'sysdate+1/1440'); __| |__ |___ |____________ | | | | job号 what next_date interval
commit;
DBMS_Job包含以下子过程：
1、Broken()过程:更新一个已提交的工作的状态，可用于临时停止任务
PROCEDURE BROKEN ( JOB IN BINARY_INTEGER, BROKENIN BOOLEAN, NEXT_DATE IN DATE := SYSDATE )
2、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 );
3、Interval()过程:用来显式地设置重执行一个工作之间的时间间隔数。
PROCEDURE INTERVAL ( JOB IN BINARY_INTEGER, INTERVAL IN VARCHAR2 )
4、Isubmit()过程:用来用特定的工作号提交一个工作
PROCEDURE ISUBMIT ( JOB IN BINARY_INEGER, WHAT IN VARCHAR2, NEXT_DATE IN DATE, INTERVAL IN VARCHAR2, NO_PARSE IN BOOEAN := FALSE )
5、Next_Date()过程:用来显式地设定一个工作的执行时间
PROCEDURE NEXT_DATE ( JOB IN BINARY_INEGER, NEXT_DATE IN DATE )
6、Remove()过程:来删除一个已计划运行的工作
PROCEDURE REMOVE(JOB IN BINARY_INEGER);
7、Run()过程:用来立即执行一个指定的工作
PROCEDURE RUN(JOB IN BINARY_INEGER)
8、Submit()过程：工作被正常地计划好
PROCEDURE SUBMIT ( JOB OUT BINARY_INEGER, WHAT IN VARCHAR2, NEXT_DATE IN DATE, INTERVAL IN VARCHAR2, NO_PARSE IN BOOEAN := FALSE )
9、User_Export()过程：返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。
PROCEDURE USER_EXPORT ( JOB IN BINARY_INEGER, MY_CALL IN OUT VARCHAR2 )
10、What()过程：可以用于更改要执行的任务
PROCEDURE WHAT ( JOB IN BINARY_INEGER, WHAT IN OUT VARCHAR2 )
整理完上面的后，想想，以上这些难道要死记？要背手册？
其实...可以通过desc获得：
很多的dbms工具包都可以通过desc查看
SQL> desc dbms_job; FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN PROCEDURE BROKEN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN BROKEN BOOLEAN IN NEXT_DATE DATE IN DEFAULT PROCEDURE CHANGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT PROCEDURE INSTANCE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN INSTANCE BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULT PROCEDURE INTERVAL Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN INTERVAL VARCHAR2 IN PROCEDURE ISUBMIT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT FUNCTION IS_JOBQ RETURNS BOOLEAN PROCEDURE NEXT_DATE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN NEXT_DATE DATE IN PROCEDURE REMOVE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN PROCEDURE RUN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULT PROCEDURE SUBMIT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER OUT WHAT VARCHAR2 IN NEXT_DATE DATE IN DEFAULT INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT PROCEDURE USER_EXPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUT PROCEDURE USER_EXPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUT MYINST VARCHAR2 IN/OUT PROCEDURE WHAT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN
