Oraclejob用法总结.doc

神州泰岳JOB用法总结北京神州泰岳软件股份有限公司Oracle JOB 用法总结北京神州泰岳软件股份有限公司2010年5月文档属性属性内容客户名称北京神州泰岳软件股份有限公司项目名称项目质量管理规范项目编号文档主题job用法总结文档编号文档版本1.0文档日期文档状态草案作者张少雷文档变更版本修订日期修订人描述1.02010-05-28张少雷文档送呈姓名目的目录1.设置初始化参数 job_queue_processes32.Job的相关操作32.1Submit() 创建job42.2Run() 立即执行job52.3Remove()删除job52.4Next_Date()修改job的next date时间62.5Interval()修改job的运行周期72.6What()修改job中what参数指定的PL/SQL代码72.7broken()中断job的运行82.8启用job92.9change()修改job的参数92.10Isubmit()102.11User_Export()103.Job运行周期111. 设置初始化参数 job_queue_processesJob_queue_processes指定可以并发运行的job数Alter system set Job_queue_processes=n scope=both;该参数的值最好大于instance下的job数量2. Job的相关操作通过dbms_job包来管理job，该包含有如下存储过程: Submit()、Run()、Remove()、change()、Broken()、What()、Next_Date()、Interval()、Isubmit()、User_Export()2.1 Submit() 创建jobsubmit包含五个参数：job、what、next_date、interval与no_parseSubmit ( jobOUT binary_ineger,WhatINvarchar2,next_date INdate,intervalINvarchar2,no_parseINbooean:=FALSE)job参数是由Submit()过程返回的job号，次参数是个变量，在使用前先声明一下,job号在dba_jobs视图里可以查到。what参数是将被执行的PL/SQL代码块，一般是存储过程的名字next_date参数指定运行job的时间interval参数指定job的运行周期no_parse参数指定何时对what参数指定的PL/SQL进行语法分析。TRUE表示在job第一次执行时应进行检查，FALSE指示在创建job时进行检查。默认是false。注意：next_date是时间类型，interval是字符类型，在调用submit时要指定正确的参数类型。样例:declare jobno number;begin dbms_job.submit(job=jobno,what=a;,next_date=sysdate,interval=sysdate+1/1440); -默认no_parse为falsecommit;end;declare jobno number;begin dbms_job.submit(job=jobno,what=a;,next_date=sysdate,interval=sysdate+1/1440,no_parse = true);commit;end;2.2 Run() 立即执行joba)查询job为64的last_date和next_dateselect job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT64 2010-4-12 14:00:292010-4-12 14:02:29Nsysdate+2/1440a;b)立即运行jobSQL exec dbms_job.run(64);PL/SQL procedure successfully completedSQL c)查询job为64的last_date和next_dateselect job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 14:00:512010-4-12 14:02:51运行dbms_job.run后job的next_date会立刻变成系统的当前时间，启动jobNsysdate+2/1440a;2.3 Remove()删除joba)查询job号为41的jobselect job,last_date,next_date,broken,interval,what from dba_jobs where job=41;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT41 2010-4-9 13:51:094000-1-1Ysysdate+5/1440update_;b)删除jobSQL exec dbms_job.remove(41);PL/SQL procedure successfully completedSQL commit;Commit completeSQLc)查询是否还有job号为41的jobselect job,last_date,next_date,broken,interval,what from dba_jobs where job=41;无记录2.4 Next_Date()修改job的next date时间a)查询job号为64的whatselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:37:022010-4-12 15:40:32Nsysdate+1/1440insert_;2010-4-12 15:37:02b)更改next date时间SQL exec dbms_job.next_date(64,next_date=sysdate);#sysdate=2010-4-12 15:37:12PL/SQL procedure successfully completedSQL commit;Commit completec)查询job号为64的jobselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:37:122010-4-12 15:38:12N此时不管job的运行周期是多长:(next date - last date)1个job周期，job都会在新指定的next date时间点运行jobsysdate+1/1440insert_;2010-4-12 15:37:12d)用to_date指定next date时间SQL exec dbms_job.next_date(64,next_date=to_date(2010-04-09 15:40:22,yyyy-mm-dd hh24:mi:ss);PL/SQL procedure successfully completedSQL commit;Commit completeSQLe)查询job号为64的jobselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:40:222010-4-12 15:41:22Nsysdate+1/1440insert_;2010-4-12 15:40:222.5 Interval()修改job的运行周期a)查询job号为64的jobselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:45:232010-4-12 15:46:23Nsysdate+1/1440insert_;2010-4-12 15:45:23b)修改job的运行周期SQL exec dbms_erval(64,sysdate+2/1440);PL/SQL procedure successfully completedSQL commit;Commit completeSQLc)查询job号为64的jobselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:47:232010-4-12 15:49:23Nsysdate+2/1440insert_;2010-4-12 15:47:232.6 What()修改job中what参数指定的PL/SQL代码a)查询job号为64的whatJOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 13:53:232010-4-12 13:56:27Nsysdate+2/1440 update_;b)修改what为a;SQL exec dbms_job.what(64,a;);PL/SQL procedure successfully completedSQL commit;Commit completec)查询job号为64的whatJOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 13:56:292010-4-12 13:58:29Nsysdate+2/1440a;2.7 broken()中断job的运行broken有三个参数：job 、broken与next_date。 job参数是job号，broken参数指示此job是true或falsenext_date参数指定job何时运行，这个是时间类型的参数。a)查询job为64的jobselect job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 14:21:302010-4-12 14:23:30Nsysdate+2/1440a;b)中断jobSQL exec dbms_job.broken(64,true,to_date(2010-4-9 12:43:59,yyyy-mm-dd hh24:mi:ss);PL/SQL procedure successfully completedSQL commit;Commit completeSQL或SQL exec dbms_job.broken(64,true,sysdate);PL/SQL procedure successfully completedSQL commit;Commit completeSQLc)查询job为64的jobselect job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 14:31:324000-1-1当中断job后，next_date就会变成4000-1-1，broken也会置为Y. 此时next date为sysdate或是其他时间，这个都是不起作用的。Ysysdate+2/1440a;2.8 启用joba)查询job为64的jobselect job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 14:31:324000-1-1Ysysdate+2/1440a;b)启用jobSQL exec dbms_job.broken(64,false,to_date(2010-4-12 14:42:32,yyyy-mm-dd hh24:mi:ss);PL/SQL procedure successfully completedSQL exec dbms_job.broken(64,true,sysdate);SQL commit;Commit completeSQLc)查询job为64的jobselect job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 14:31:322010-4-12 14:42:32取消job的broke后，next_date就会变成参数中指定的具体nextdate时间，broken置为NNsysdate+2/1440a;select job,last_date,next_date,broken,interval,what from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHAT642010-4-12 14:42:322010-4-12 14:44:32Nsysdate+2/1440a;2.9 change()修改job的参数change()有4个参数：job、what 、next_date与interval。a)查询job为64的jobselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:33:022010-4-12 15:35:02Nsysdate+2/1440a;2010-4-12 15:34:06b)修改job的next_date,interval,whatSQL exec dbms_job.change(64,insert_;,to_date(2010-4-12 15:40:32,yyyy-mm-dd hh24:mi:ss),sysdate+1/1440);PL/SQL procedure successfully completedSQL commit;Commit completec)查询job为64的jobselect job,last_date,next_date,broken,interval,what,sysdate from dba_jobs where job=64;JOBLAST_DATENEXT_DATEBROKENINTERVALWHATSYSDATE642010-4-12 15:37:022010-4-12 15:40:32Nsysdate+1/1440insert_;2010-4-12 15:38:182.10 Isubmit()Isubmit()与submit的参数都是一样的，唯一的区别是job参数是人为指定的一个job号，而submit的job是不需要指定的，又数据库自动产生一个job号。当使用isubmit时如果指定的job号已经存在，则会报一个违反唯一性约束的错误。a)创建jobdeclare jobno number;begindbms_job.isubmit(job=100,what=a;,next_date=sysdate,interval=sysdate+1/1440,no_parse = true);commit;end;b)查询jobselec