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、创建JOB
variable v_job_no number;
begin
dbms_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 as
begin
insert into a values(sysdate);
end;
/
3. 创建JOB（任务计划）
variable job1 number;
begin
dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440');
end;
/
--每天1440分钟，即一分钟运行test存储过程一次
4. 运行JOB
begin
dbms_job.run(:job1);
end;
/
5. 查看执行结果
select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间from a;
6.删除JOB
begin
ve(:job1);
end;
/
job的使用说明：
DBMS_JOB.SUBMIT(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:ve(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间：_date(job,next_date);
修改间隔时间：dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子：
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;',//Procdemo为过程名称
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
2. DBMS_SCHEDULER
DBMS_SCHEDULER的功能更强大，定义更灵活，增强了与系统的交互性。可以有两种方式来定义"计划"：
1) 使用DBMS_SCHDULER.CREATE_SCHEDULE //定义一个计划，计划再调用job;
2) 调用DBMS_SCHDULER.CREATE_JOB //过程直接定义job
例子1：用DBMS_SCHDULER.CREATE_JOBE直接创建job
BEGIN
DBMS_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, //启动该job
comments => '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：
BEGIN
DBMS_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 able ('zbb_job3');
例子3：使用DBMS_SCHDULER.CREATE_SCHEDULE
建立 scheduler（控制时间和频率），然后给它添加几个jobs（动作）！
oracle手动调用存储过程BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE ( //创建计划任务
schedule_name => 'zbb_schedule',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', //执行间隔：每5分钟 comments => 'Every 5 MINUTS');
END;
/
接下来往“zbb_schedule”里添加一个job “zbb_job2”：
BEGIN
DBMS_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”：
BEGIN
DBMS_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。
也可以将“zbb_job1”加入到“zbb_schedule”
SQL> begin
2 dbms_scheduler.SET_ATTRIBUTE( //注意这里执行的是修改属性的过程
3 name => 'zbb_job1',
4 attribute => 'schedule_name',
5 value => 'ZBB_SCHEDULE');
6 end;
7 /
删除 job 及 scheduler：
SQL> BEGIN
DBMS_SCHEDULER.DROP_JOB ( //删除job；多个job间用逗号隔开
job_name => 'zbb_job2,zbb_job3',
force => TRUE);
END;
/
如何删除scheduler：
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE (
schedule_name => 'zbb_schedule',
force => true ); // 设为true 强制；false。
END;
/
如何修改job的属性（frequency：频率）：
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'zbb_job1',
attribute => 'repeat_interval',
value => 'FREQ=DAILY'); //每天执行一次
END;
/
jobs创建时为“disabled”状态。我们必须手动“enable”它:
BEGIN
DBMS_SCHEDULER.ENABLE ('job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jobclass3'); /* sys.jobclass1下的所有jobs都会被enable */END;/ END;
/
查看job的状态：
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_EMP_JOB1';
查看Job Logs：
SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;
查看Job 运行的详细情况：
select log_id, job_name, status, to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date from dba_scheduler_job_run_detailswhere job_name = 'MY_JOB14';
如何管理计划任务的权限：
GRANT SCHEDULER_ADMIN TO username;GRANT CREATE JOB TO scott;
GRANT ALTER myjob1 TO scott;GRANT MANAGE SCHEDULER TO adam;
PLSQL举例如下：
begin
ate_job (
job_name => '测试',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into job_test values(sysdate);',
start_date => sysdate,
end_date => add_months(sysdate,12000),
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
enabled => true,
发表评论