Oracle定时执行计划任务

Oracle定时执行计划任务Oracle在10g版本以前，计划任务用的是DBMS_JOB包，10g版本引入DBMS_SCHEDULER 来替代先前的DBMS_JOB,在功能方面,它比DBMS_JOB提供了更强大的功能和更灵活的机制管理，但DBMS_JOB包的使用相对比较简单，也基本能够满足定时执行计划任务的需求，故接下来就先看看DBMS_JOB包的使用方法。

1.DBMS_JOB我们可以在命令窗口输入show parameter job_queue_processes查看数据库中定时任务的最多并发数，一般设置为10（设置方法：alter system set job_queue_processes=10），如果设为0，那么数据库定时作业是不会运行的。

oracle定时执行job queue 的后台进程是SNP,要启动snp,首先看系统模式是否支持sql> alter system enable restricted session;或sql> alter system disenable restricted session;利用上面的命令更改系统的会话方式为disenable restricted,为snp的启动创建条件.接下来我们尝试实现以下功能：每隔一分钟自动向job_test表中插入当前的系统时间。

1、创测试表create table job_test(updatetime date);2、创建JOBvariable v_job_no number;begindbms_job.submit(:v_job_no, 'insert into job_test values(sysdate);', sysdate, 'sysdate+1/1440');end;/其中最后一个参数'sysdate+1/1440'表示时间间隔为每分钟。

其它常用的时间间隔的设置如下：（1）如果想每天凌晨1点执行，则此参数可设置为'trunc(sysdate)+25/24'；（2）如果想每周一凌晨1点执行，则此参数可设置为'trunc(next_day(sysdate,1))+25/24'；（3）如果想每月1号凌晨1点执行，则此参数可设置为'trunc(last_day(sysdate))+25/24'；（4）如果想每季度执行一次，则此参数可设置为'trunc(add_months(sysdate,3),'Q')+1/24'；（5）如果想每半年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),6)+1/24'；（6）如果想每年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),12)+1/24'。

select * from user_jobs;--查看当前用户的调度任务select * from dba_jobs_running;--查看正在执行的调度任务select * from dba_jobs;--查看执行完的调度任务select * from all_jobs; -- 查看数据库系统所有任务实例1：1. 创建测试表create table a(a date);2. 创建一个存储过程create or replace procedure test asbegininsert into a values(sysdate);end;/3. 创建JOB（任务计划）variable job1 number;begindbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440');end;/--每天1440分钟，即一分钟运行test存储过程一次4. 运行JOBbegindbms_job.run(:job1);end;/5. 查看执行结果select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间from a;6.删除JOBbegindbms_job.remove(:job1);end;/job的使用说明：DBMS_JOB.SUBMIT(:jobno,//job号'your_procedure;',//要执行的过程trunc(sysdate)+1/24,//下次执行时间'trunc(sysdate)+1/24+1'//每次间隔时间);删除job:dbms_job.remove(jobno);修改要执行的操作:job:dbms_job.what(jobno,what);修改下次执行时间：dbms_job.next_date(job,next_date);修改间隔时间：dbms_job.interval(job,interval);停止job:dbms.broken(job,broken,nextdate);启动job:dbms_job.run(jobno);例子：VARIABLE jobno number;beginDBMS_JOB.SUBMIT(:jobno,'Procdemo;',//Procdemo为过程名称SYSDATE, 'SYSDATE + 1/720');commit;end;/2. DBMS_SCHEDULERDBMS_SCHEDULER的功能更强大，定义更灵活，增强了与系统的交互性。

可以有两种方式来定义"计划"：1) 使用DBMS_SCHDULER.CREATE_SCHEDULE //定义一个计划，计划再调用job;2) 调用DBMS_SCHDULER.CREATE_JOB //过程直接定义job例子1：用DBMS_SCHDULER.CREATE_JOBE直接创建jobBEGINDBMS_SCHEDULER.CREATE_JOB (job_name => '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', //这里把时区去掉也可以！repeat_interval => 'FREQ=DAILY;INTERVAL=2', /* every other day */enabled =>true, //启动该jobcomments => 'My new job'); //注释END;/参数说明：·job_name: 顾名思义,每个job都必须有一个的名称·schedule_name: 如果定义了计划，在这里指定计划的名称·job_type: 目前支持三种类型:·PLSQL_BLOCK : PL/SQL块: 需要输入完整的PL/SQL代码;·STORED_PROCEDURE : 存储过程: 需要指定存储过程的名字;·EXECUTABLE: 外部程序: (外部程序可以是一个shell脚本,也可以是操作系统级别的指令). 需要输入script的名称或者操作系统的指令名·enabled: 上面已经说过了，指定job创建完毕是否自动激活·comments: 对于job的简单说明例子2：BEGINDBMS_SCHEDULER.CREATE_JOB (job_name => 'zbb_job3',job_type => 'PLSQL_BLOCK', //job的类型是执行sql语句job_action => 'insert into bb values(1,sysdate);',start_date => sysdate,repeat_interval => 'freq = minutely; interval=1', //每分钟执行一次enabled => true,comments => 'my love');END;/注意： enabled 默认为false，oracle不会运行此job，所有我们需要enable它 SQL> exec dbms_scheduler.enable ('zbb_job3');例子3：使用DBMS_SCHDULER.CREATE_SCHEDULE建立 scheduler（控制时间和频率），然后给它添加几个jobs（动作）！BEGINDBMS_SCHEDULER.CREATE_SCHEDULE ( //创建计划任务schedule_name => 'zbb_schedule',repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', //执行间隔：每5分钟 comments => 'Every 5 MINUTS');END;/接下来往“zbb_schedule”里添加一个job “zbb_job2”：BEGINDBMS_SCHEDULER.CREATE_JOB ( //创建job 并把它加入到scheduler里面job_name => 'zbb_job2',job_type => 'PLSQL_BLOCK',job_action => 'insert into bb values(1,sysdate);',schedule_name => 'ZBB_SCHEDULE');END;/再往“zbb_schedule”里添加一个job “zbb_job3”：BEGINDBMS_SCHEDULER.CREATE_JOB (job_name => 'zbb_job3',job_type => 'PLSQL_BLOCK',job_action => 'insert into bb values(1,sysdate);',schedule_name => 'ZBB_SCHEDULE');END;/运行“select job_name,schedule_name from user_scheduler_jobs;”可以查看到所有的job。