一点一点学习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等。

想细致了解的请前往

在使用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;beginsys.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      NSQL> select count(*) from a;  COUNT(*)----------         9SQL> /  COUNT(*)----------        10SQL> /  COUNT(*)----------        10SQL> select * from a;A-------------------2013-05-31 15:45:322013-05-31 15:48:582013-05-31 15:49:032013-05-31 15:49:082013-05-31 15:49:132013-05-31 15:49:182013-05-31 15:49:232013-05-31 15:49:282013-05-31 15:49:332013-05-31 15:49:382013-05-31 15:49:4311 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       NSQL> truncate table a;Table truncated.SQL> select * from a;no rows selectedSQL> select * from a;A-------------------2013-05-31 15:57:08SQL> /A-------------------2013-05-31 15:57:082013-05-31 15:58:082013-05-31 15:59:082013-05-31 16:00:082013-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:25SQL> 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:092013-05-31 16:09:092013-05-31 16:10:092013-05-31 16:11:092013-05-31 16:12:042013-05-31 16:13:042013-05-31 16:14:042013-05-31 16:15:042013-05-31 16:16:042013-05-31 16:17:042013-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/1440SQL> 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 selectedSQL> 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:002)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:004)SQL> select trunc(sysdate,'dd') from dual;  --dd返回当前年月日，与trunc(sysdate)等效TRUNC(SYSDATE,'DD')-------------------2013-06-04 00:00:005)SQL> select trunc(sysdate,'yyyy') from dual; --yy跟yy一样，返回当年第一天TRUNC(SYSDATE,'YYYY-------------------2013-01-01 00:00:006)SQL> select trunc(sysdate,'d') from dual;    --d返回本周第一天，按老美的，第一天是周日TRUNC(SYSDATE,'D')-------------------2013-06-02 00:00:007)SQL> select trunc(sysdate, 'hh') from dual ; --hh返回当前时间，精确到TRUNC(SYSDATE,'HH')-------------------2013-06-04 16:00:008)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:00SQL> 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再组合下truncSQL> 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;--按老美的每周第一天是周日，所以周三是4NEXT_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:00SQL>  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 200SQL> col job format 999SQL> col what format a50SQL> col interval format a50SQL> col next_date format a50SQL> 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;begindbms_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;begindbms_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 BOOLEANPROCEDURE BROKEN Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN BROKEN                         BOOLEAN                 IN NEXT_DATE                      DATE                    IN     DEFAULTPROCEDURE 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     DEFAULTPROCEDURE INSTANCE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN INSTANCE                       BINARY_INTEGER          IN FORCE                          BOOLEAN                 IN     DEFAULTPROCEDURE INTERVAL Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN INTERVAL                       VARCHAR2                INPROCEDURE 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     DEFAULTFUNCTION IS_JOBQ RETURNS BOOLEANPROCEDURE NEXT_DATE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN NEXT_DATE                      DATE                    INPROCEDURE REMOVE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          INPROCEDURE RUN Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN FORCE                          BOOLEAN                 IN     DEFAULTPROCEDURE 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     DEFAULTPROCEDURE USER_EXPORT Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN MYCALL                         VARCHAR2                IN/OUTPROCEDURE USER_EXPORT Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN MYCALL                         VARCHAR2                IN/OUT MYINST                         VARCHAR2                IN/OUTPROCEDURE WHAT Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB                            BINARY_INTEGER          IN WHAT                           VARCHAR2                IN