oracle计划任务

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

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');　　--每天1440分钟，即一分钟运行test过程一次
end;
/
 
 
4.运行JOB
begin
dbms_job.run(:job1);
end;
/
 

5.查看
select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;

时间
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24

6.删除JOB
begin
dbms_job.remove(:job1);
end;
/
 

job的使用：
select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') from all_jobs;
 
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;
begin
      DBMS_JOB.SUBMIT(:jobno,
              'Procdemo;',//Procdemo为过程名称
               SYSDATE, 'SYSDATE + 1/720');
         commit;
end;

-----修改job_queue_processes的值（保证其不为0否则JOB不自动运行)
可通过select * from v$parameter;查看其值；
方法1，startup pfile='C:/oracle/ora90/database/initorcl.ora';
需要修改initorcl.ora文件的job_queue_processes参数，然后重新启动数据库以后才能生效

方法2，alter system set job_queue_processes=10
不需要重新启动数据库就能生效，系统自动修改init.ora文件


1)、 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)

2)、 每天定时执行
例如：每天的凌晨2点执行
Interval => TRUNC(sysdate) + 1 +2 / (24)

3)、 每周定时执行
例如：每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

4)、 每月定时执行
例如：每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

5)、 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

6)、 每半年定时执行
例如：每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

7)、 每年定时执行
例如：每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24


描述 Interval参数值
每天运行一次
'SYSDATE + 1'

每小时运行一次
'SYSDATE + 1/24'

每10分钟运行一次
'SYSDATE + 10/（60*24）'

每30秒运行一次
'SYSDATE + 30/(60*24*60)'

每隔一星期运行一次
'SYSDATE + 7'

不再运行该任务并删除它
NULL

每天午夜12点
'TRUNC(SYSDATE + 1)'

每天早上8点30分
'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'

每星期二中午12点
'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'

每个月第一天的午夜12点
'TRUNC(LAST_DAY(SYSDATE ) + 1)'

每个季度最后一天的晚上11点
'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'

每星期六和日早上6点10分
'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'



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 dbms_scheduler.enable ('zbb_job3');

例子3：使用DBMS_SCHDULER.CREATE_SCHEDULE
建立 scheduler（控制时间和频率），然后给它添加几个jobs（动作）！
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 dbms_scheduler.create_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,
comments => '定时执行计划任务' );
end;
以上代码可以通过在PLSQL Developer中新建Jobs轻松设定：
参数含义如下：
?JOB_NAME ：指定任务的名称，必选值，注意要确保指定的名称唯一。
?JOB_TYPE ：任务执行的操作类型，必选值，有下列几个可选值：
?PLSQL_BLOCK ：表示任务执行的是一个PL/SQL匿名块。
?STORED_PROCEDURE ：表示任务执行的是ORACLE过程(含PL/SQL PROCEDURE和JAVA PROCEDURE)。
?EXECUTABLE ：表示任务执行的是一个外部程序，比如说操作系统命令。
?CHAIN ：表示任务执行的是一个CHAIN。
?JOB_ACTION ：任务执行的操作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/SQL匿名块，此处就可以放置PL/SQL块的具体代表，类似DECLARE .. BEGIN ..END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。
?START_DATE ：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。
?REPEAT_INTERVAL ：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。 REPEAT_INTERVAL参数需要好好说说，REPEAT_INTERVAL


使用PL/SQL创建计划任务：
Jobs--> 新建 在编辑窗口 填写三个参数：what、下一个日期、间隔，点击“应用”，即创建成功，时间点到达，oracle数据库会调用what里面的操作。

例子：
what：
begin
DELETE_TABLE_NUM('report_netunit_interfaces',1000000);
end;

下一个日期：
2014/4/14 18:00:00

间隔：
sysdate+1