Oracle定时执行计划任务.

1、Oracle 定时执行计划任务Oracle 在 10g 版本以前,计划任务用的是 DBMS_JOB 包，10g 版本引入DBMS_SCHEDULER 来替代先前的 DBMS_JOB,在功能方面，它比 DBMS_JOB 提 供了更强大的功能和更灵活的机制 管理，但 DBMS_JOB 包的使用相对比较简单,也 基本能够满足定时执行计划任务的需求,故接下来就先看看 DBMS_JOB 包的使用 方法。1. DBMS_JOB我们可以在命令窗口输入 show parameter job_queue_processe 查看数据库中定 时任务 的最多并发数,一般设置为 10(设置方法：alter system

2、 set job_queue_processes=10如果 设为 0,那么数据库定时作业是不会运行的。oracle 定时执行 job queue 的后台进程是 SNP ,要启动 snp,首先看系统模式是 否支持 sql alter system en able restricted session? sql alter system dise nable restrictedsessi on 利用上面的命令更改系统的会话方式为dise nable restricted 为 snp的启动创建条件接下来我们尝试实现以下功能：每隔一分钟自动向 job_test 表中插入当前的系统 时间。1、创测试表

3、create table job_test(updatetime date;2、创建 JOBvariable v_job_ no nu mber;begi ndbms_job.submit(:v_j ob_no, in sert into job_test values(sysdate;, sysdate,sysdate+1/1440;end;/其中最后一个参数sysdate+1/1440 表示时间间隔为每分钟。其它常用的时间间 隔的设置如下：(1 如果想每天凌晨 1 点执行，则此参数可设置为trunc(sysdate+25/24;(2 如果想每周一凌晨 1 点执行，则此参数可设置为tru n

4、c( next_day(sysdate,1+25/24;(3 如果想每月 1 号凌晨 1 点执行,则此参数可设置为tru nc(last_day(sysdate+25/24;(4 如果想每季度执行一次，则此参数可设置为tru nc(add_mo nths(sysdate,3,Q+1/24;SQL J mho谢psram etfer j ob_qntu_pr r.ct s sesNAMEmiVALUEjraciEEE10system set jS5=1O2 /System(5 如果想每半年执行一次,则此参数可设置为add_ mon ths(tru nc(sysdate,yyyy,6+1/24;(

5、6 如果想每年执行一次，则此参数可设置为add_mo nths(tru nc(sysdate,yyyy,12+1/24select * from user_jobs;-查看当前用户的调度任务select * from dba_jobs_ru nnin g;-查看正在执行的调度任务select * from dba_jobs;-查看执行完的调度任务select * from all_jobs;-查看数据库系统所有任务实例 1：1. 创建测试表create table a(a date;2. 创建一个存储过程create or replace procedure test asbegi nin s

6、ert into a values(sysdate;end;/3. 创建 JOB (任务计划variable job1 nu mber;begi ndbms_job.submit(:job1,test;,sysdate,sysdate+1/1440; end;-每天 1440 分钟,即一分钟运行 test 存储过程一次4. 运行 JOBbegi ndbms_job.r un(:jobl;end;/5. 查看执行结果select to_char(a,yyyy/mm/dd hh24:mi:ss 时间 from a; 6.删除 JOB begi ndbms_job.remove(:job1;end;

7、/job 的使用说明：DBMS_JOB.SUBMIT(:jobno,/job 号your_procedure;,/要 执行的过程trun c(sysdate+1/24,/下 次执行时间tru nc(sysdate+1/24+17 每次间隔时间删除 job:dbms_job.remove(job no;修改要执行的操作：job:dbms_job.what(jobno,what;修改下次执行时间:dbms_job.next_date(job,next_date; 修改间隔时间:dbms_erval(job,interval;停止 job:dbms.broke n(job,broke

8、n,n extdate;启动 job:dbms_job.r un (job no;例子：VARIABLE job no number;begi nDBMS_JOB.SUBMIT(:job no,Procdemo;,/Procdemo 为过程名称SYSDATE, SYSDATE + 1/720;commit;end;描述Interval参数值每尺运行一次*SYSDATE + lr每小时运行一次*SYSDATE + 1/24*每10分钟运行次*SYSDATE + 10/ (60* 24 J *每30杪运行一次*SYSDATE + 30/(60*24*60)r每隔一星期运行一次*SYSDATE +

9、T不再运行该任务井删除它NULL每天午夜12点RUNCfSYSDATE + l)1每夭早上8点30分RUNCfSYSDATE + 1) +(8*60+30) /(24*60)*每星期二中午12点* N EXT DAY( T RU NC( SYS DAT E ), TUESDAY ) + 12/2每个力第-天的午夜匹点*TRU NC(LAST DAY( SYS DATE ) + l)1每个李度最后一处的晚上11点tTRUNC(ADD MONTHS(SYSDATE + 2/24, 2 ), Q ) 1每星期六和日早上6点10分*TRUNC( LEAST( NEXT_DAY(SYSDATE, *SA

10、TU RDAY,)/NEXT-DAYfSYSDATE,ISUNDAYH) +(6x60+10) /(24x 60)12. DBMS_SCHEDULERDBMS_SCHEDULER 的功能更强大，定义更灵活，增强了与系统的交互性。可以 有两种方 式来定义计划:1 使用 DBMS_SCHDULER.CREATE_SCHEDULE /定义一个计戈 U，计戈 U 再调用 job;2 调用 DBMS_SCHDULER.CREATE_JOB / 过程直接定义 job例子 1:用 DBMS_SCHDULER.CREATE_JOBE 直接创建 jobBEGINDBMS_SCHEDULER.CREATE_JOB

11、 (job_n ame = update_bb, /job 的名字job_type = STORED_PROCEDURE, /job 的类型为 执行存储过程 ” job_action =OPS.SALES_PKG.UPDATE_SALES_SUMMARY, / 存储过程的 名字start_date = 28-APR-10 07.00.00 PM Australia/Sydney,这里把时区去掉也可 以！repeatnterval = FREQ=DAILY;INTERVAL=2, /* every other day */enabled =true, /启动该 jobcomme nts = My

12、 new job; 注释END;/参数说明：job_name:顾名思义，每个 job 都必须有一个的名称schedule_name:如果定义了计划，在这里指定计划的名称job_type:目前支持三种类型：PLSQL_BLOCK : PL/SQL 块：需要输入完整的 PL/SQL 代码；STORED_PROCEDURE :存储过程：需要指定存储过程的名字；EXECUTABLE:外部程序：（外部程序可以是一个 shell 脚本，也可以是操作系 统级别的指令.需要输入 script 的名称或者操作系统的指令名enabled:上面已经说过了，指定 job 创建完毕是否自动激活comme nts:对于

13、job 的简单说明例子 2:BEGINDBMS_SCHEDULER.CREATE_JOB (job_ name = zbb_job3,job_type = PLSQL_BLOCK, /job 的类型是执行 sql 语句 job_action = insertinto bb values(1,sysdate;,start_date = sysdate,repeat_interval = freq = minutely; interval=1, 每分钟执行一 次en abled = true,comme nts = my love;END;/注意：enabled 默认为 false , oracl

14、e 不会运行此 job，所有我们需要 enable 它SQL exec dbms_scheduler.e nable (zbb_job3;例子 3:使用 DBMS_SCHDULER.CREATE_SCHEDULE建立 scheduler 控制时间和频率撚后给它添加几个 jobs (动作！BEGINDBMS_SCHEDULER.CREATE_SCHEDULE ( / 仓 U 建计戈 U 任务schedule_ name = zbb_schedule,repeat_interval = FREQ=MINUTELY; INTERVAL=5, / 执行间隔：每 5 分钟 comme nts = Eve

15、ry 5MINUTS;END;/接下来往 “ zbb_schedul 里添加一个 job “ zbb_job2 ”：BEGINDBMS_SCHEDULER.CREATE_JOB ( / 创建 job 并把它加入到 scheduler 里面 job_n ame =zbb_job2,job_type = PLSQL_BLOCK,job_action = in sert into bb values(1,sysdate;,schedule_name = ZBB_SCHEDULE;END;/再往 “ zbb_schedul 里添加一个 job “ zbb_job3 ”：BEGINDBMS_SCHEDU

16、LER.CREATE_JOB (job_n ame = zbb_job3,job_type = PLSQL_BLOCK,job_action = in sert into bb values(1,sysdate;,schedule_name = ZBB_SCHEDULE;END;运行 “ select job_name,schedule_name from user_scheduler_jo!S 以查看到所有 的 job。也可以将 “ zbb_job1 加入到 “ zbb_schedule ”SQL begi n2 dbms_scheduler.SET_ATTRIBUTE( 注意这里执行的是修

17、改属性的过程3 n ame = zbb_job1,4 attribute = schedule, name,5 value = ZBB_SCHEDULE;6 end;7 /删除 job 及 scheduler:SQL BEGINDBMS_SCHEDULER.DROP_JOB ( / 删除 job ;多个 job 间用逗号隔开job_ name = zbb_job2,zbb_job3,force = TRUE;END;/如何删除 scheduler :BEGINDBMS_SCHEDULER.DROP_SCHEDULE ( schedule_name = zbb_schedule, force E

18、ND; / 如何修改 job 的属性(frequency:频率)：BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( n ame attribute valueEND; / = = = zbb_job1, repeatnterval, FREQ=DAILY; / 每天执行一次= true ; / 设为 true 强 制；falseo jobs 创建时为“disabled 犬态。我们必须手动 “enable 它：BEGIN DBMS_SCHEDULER.ENABLE(job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jo

19、bclass3; /* sys.jobclass1 下的所有 jobs 都会被enable */END;/ END; / 查看 job 的状态：SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBSWHERE JOB_NAME = MY_EMP_JOB1;查看 Job Logs： SELECT JOB_NAME, OPERATION, OWNERFROM DBA_SCHEDULER_JOB_LOG;查看 Job 运行的详细 情况：select log_id, job_name, status,to_char(log_date, DD-MON-YYYY HH

20、24: MI log_date from dba_scheduler_job_ru n_detailswhere job_name = MY_JOB14 如何管理 计划任务的权限： GRANT SCHEDULER_ADMIN TO username;GRANTCREATEJOB TO scott; GRANT ALTER myjob1 TO scott;GRANT MANAGE SCHEDULER TO adam; PLSQL 举例如下： beg in dbms_scheduler.create_job ( job_ name =测试, job_type = PLSQL_BLOCK, job_

21、action= in sert into job_test values(sysdate;, start_date = sysdate, en d_date = add_m on ths(sysdate,12000,repeat_i nterval = FREQ=MINUTELY; INTERVAL=1, en abled = true,comments =定时执行计划任务;end;以上代码可以通过在 PLSQL Developer 中新建 Jobs 轻松设定：参数含义如下：？JOB_NAME :指定任务的名称，必选 值，注意要确保指定的名称唯一。？JOB_TYPE :任务执行的操作类型，必选

22、值，有下列几个可选值：?PLSQL_BLOCK :表示任务执行的是一个 PL/SQL 匿名块。?STORED_PROCEDURE :表示任务执行的是 ORACLE 过程（含 PL/SQL PROCEDURE 和 JAVAPROCEDURE。EXECUTABLE :表示任务执行的是一个 外部程序，比如说操作系统命令。?CHAIN :表示任务执行的是一个 CHAIN。?JOB_ACTION :任务执行的操作， 必选值， 应与 JOB_TYPE 类型中指定的参数相 匹配。 比如说对于 PL/SQL匿名块，此处就可以放置 PL/SQL 块的具体代表，类似 DECLARE . BEGIN .END 这类

23、；如果是 ORACLE过程，那么此处应该指定具体 的过程名，注意由于任务 执行，即使过程中有 OUT 之类参数，实际执行时也不会 有输出的。?START_DATE :指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为 SYSDATE。?REPEAT_INTERVAL :指定任务执行的频率，比如多长时间会被触发再次执行。本参数 也可以为空，如果为空的话，就表示当前设定的任务只执行一次。REPEAT_INTERVAL 参数需要好好说说，REPEAT_INTERVAL 参数的语法结构 要复杂的多。其中最重要的是 FREQ 和 INTERVAL 两个关键字。扌

24、REQ 关键字用 来指定间隔的时间周期，可选参数有：YEARLY, MONTHLY, WEEKLY, DAILY,HOURLY, MINUTELY, SECONDLY ，分别表示年、月、周、日、时、分、秒等单 位。INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-99。例女口： REPEAT_INTERVAL=FREQ=DAILY;INTERVAL=1;表示每天执行一次，女口 果将 INTERVAL改为 7 就表示每 7 天执行一次，效果等同于FREQ=WEEKLY;INTERVAL=1 。一般来说，使用 DBMS_SCHEDULER.CREATE_JOB 创建一个 JOB,至少需要指定上述参数中 的 前 3 项。除此之外，还可以在 CREATE_JOB 时，指定下列参数：?NUMBER_OF_ARGUMENTS :指定该 JOB 执行时需要附带的参数的数量，默认 值为 0,注意当JOB_TYPE 列值为 PLSQL_BLOCK 或 CHAIN 时，本参数必须设 置为 0,因为上述两种情况 下不支持附带参数。？END_DATE :指定任务的过期 时间，默认值为 NULL。任务过期后，任务的 STATE 将自动被修改为 COMPLETED，ENABLED 被置为 FALSE。如果该参数设置为空的话，表示该任 务永不过期，将一直按照