Z.S.K.'s Records

Oracle之DBMS_SCHEDULER包管理计划任务

发表于 | 分类于

最近在分析oracle9i的statspack报告,statspack默认是不安装的,需要自己安装且要添加snapshot的定时生成计划,刚好有用到的job,schedule,还是比较强大,稍微记录下DBMS_SCHEDULER包的简单使用及参数说明.

DBMS_SCHDULER

首先,DBMS_SCHDULER(Oracle10g引入 的新功能,10g之前则可以使用DBMS_JOB包创建任务,见下文)可以创建两种任务,但也可以说是有三种,当中指的‘任务’可以是存储过程,可以是匿名PL/SQL语句块,还可以是操作系统层面的命令,在oracle10g之后,对操作系统层面的命令的支持更加完善.

  • DBMS_SCHDULER.CREATE_JOB
  • DBMS_SCHDULER.CREATE_SCHEDULE
  • DBMS_SCHDULER.CREATE_CREATE_PROGRAM

DBMS_SCHDULER.CREATE_JOB

这里以定时执行某一存储过程为例:

1
2
3
4
5
6
7
8
9
10
11
12
13
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'job_proc',
job_type => 'STORED_PROCEDURE',
job_action => 'proc_name',
-- start_date => null
-- end_date => null 
repeat_interval => 'FREQ=HOURLY; BYHOUR=9,12,15; BYMINUTE=5,15',
auto_drop => FALSE,
enabled => TRUE,
comments => 'proc automated exec');
END;
/

说明:

  • DBMS_SCHEDULER.CREATE_JOB:创建一个scheduler的job,创建job至少需要create_job这个系统权限.

  • job_name: 给job指定一个名字

  • job_type: job的类型,在3种值

    1. PL/SQL块: PLSQL_BLOCK
    2. 存储过程: STORED_PROCEDURE
    3. 外部程序: EXECUTABLE (外部程序可以是一个shell脚本,也可以是操作系统级别的指令),需要create external job 权限

  • job_action: 对应要执行的命令

  • start_date/end_date: 开始执行与结束执行的时间,如果不指定或者指定为null,则表示立即执行,参数start_date和end_date都是TIMESTAMP 类型，在输入的時候要遵循它们的格式

  • repeat_interval: 重复执行的频率,支持两种写法

    1. 兼容老版本的PL/SQL方式:例如SYSDATE+1, SYSDATE + 30/24*60,见下文DBMS_JOB部分

    2. 日历的表达方式: 分为三部分: 第一部分是频率,也就是”FREQ”这个关键字,它是必须指定的; 第二部分是时间间隔,也就是”INTERVAL”这个关键字，取值范围是1-999. 它是可选的参数; 最后一部分是附加的参数,可用于
      精确地指定日期和时间,它也是可选的参数,例如下面这些值都是合的:

      BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY
      BYHOUR,BYMINUTE,BYSECOND

      • repeat_interval => ‘FREQ=HOURLY; INTERVAL=2’: 每隔2小时运行一次job
      • repeat_interval => ‘FREQ=DAILY’: 每天运行一次job
      • repeat_interval => ‘FREQ=WEEKLY; BYDAY=MON,WED,FRI”: 每周的1,3,5运行job
      • repeat_interval => ‘FREQ=HOURLY; BYHOUR=9,12,15; BYMINUTE=5,15’: 每天的9:05, 9:15, 12:05, 12:15, 15:05 and 15:15运行job
      • FREQ=MONTHLY;BYMONTHDAY=1,5,-1: 每月的1号,5号,该月的最后一天执行,-1表示最后的意思

  • auto_drop: 任务执行完之后是否drop掉

  • enable: 默认创建的job是不会自动执行的,需要手动执行一次后才能自动执行,如果需要创建完就自动执行,则必须指定enable: true

  • comment: 添加注释/备注

DBMS_SCHDULER.CREATE_SCHDULER

我们可以只定义一个schdule,然后再创建一个Job,这个job指向schdule,相当于schdule是定义,job是执行实体

1
2
3
4
5
6
7
8
9
10
-- Create the schedule.
BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'test_hourly_schedule',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    comments        => 'Repeats hourly, on the hour, for ever.');
END;
/

上面我们只是定义了一个schdule,可以看到这里并没有指定具体要执行的是什么,具体执行实体可放在job中

1
2
3
4
5
6
7
8
9
10
-- Job defined by an existing program and schedule.
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_job_definition',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');
END;
/

上面的schedule_name即是指向schedule定义的内容(同样适用于program)

当然,可以完全像文章开头介绍DBMS_SCHDULER.CREATE_JOB一样在创建job里就指定执行实体,这样一步到位

DBMS_SCHDULER.CREATE_PROGRAM

1
2
3
4
5
6
7
begin
dbms_scheduler.create_program(
program_name=> 'DAILY_BACKUP_SH',
program_type=> 'EXECUTABLE',
program_action=> '/home/oracle/script/daily_backup.sh');
end;
/

参数跟上面类似,在此就不详述

大家可能已经注意到了,上面三个东西大体相似,一个就可以,为何要出这么多包体呢？

program与job区别:

  • program可以与job分离开来,且program可以被共用,而job是属于具体用户的
  • 从上面的create_program定义可以看出,里面是不包含开始/结束、执行重复频率的,这也就说明program可以被用户很自由地选择特定的程序在特定的时间段运行,以及自由的配置程序执行时的参数
  • 一个计划里可以没有program,对于计划来说它是可选的,但是一个计划必须要有一个job

DBMS_SCHEDULER其它相关函数

1
2
3
4
5
6
7
8
9
10
-- 手工执行
-- SQL> EXEC DBMS_SCHEDULER.RUN_JOB('DB_BACKUP');
-- 默认情况下任务是不启用的,除非创建计划时设置了(enabled=>true),so 如果计划没有启用,首先启用它:
-- EXEC dbms_scheduler.enable('DB_BACKUP');
-- 删除job
-- SQL> exec dbms_scheduler.drop_job('DB_BACKUP');
-- 删除schedule
-- SQL> exec dbms_scheduler.drop_schedule('DB_BACKUP');
-- 停止job
-- SQL> exec dbms_scheduler.stop_job('DB_BACKUP');

DBMS_JOB

DBMS_JOB.submit

这里以oracle9i定时生成statspack报告为例:

1
2
3
4
5
6
7
8
9
10
11
12
13
declare 
	id_job number; 
begin 
	dbms_job.submit(
		job => id_job, -- OUT; the job ID number that will be generated,
		what => 'schema_name.procedure_name;', -- IN; the name of the job you wish to run,
		next_date => trunc(sysdate)+1/24,  -- IN; the first time the job will be run,
		interval => 'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated,
        no_parse => True --IN BOOLEAN DEFAULT FALSE,
        instance => 0 --IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
        force => False --IN BOOLEAN DEFAULT FALSE
	); 
end;

说明:

  • job: 是该job的返回值,代表该job的id号,在修改或者是删除时会用到
  • what: 对应要执行的命令
  • next_date: 开始执行命令的时间
  • interval: 执行的频率,interval后面的最大字符长度不能超过200个字符
  • no_parse: 默认为false,如下情景时需要设置为true,如果想job在还没有创建相关的表或者是存储过程之前运行
  • instance: 默认为0,表示任何实例都能运行job,如果只想某一实例运行的话,这里需要指定实例号,如果指定的是非法的数字或者为null,则会报ora-23319错误
  • force: 与instance相关,默认为false,表示instance指定的实例必须处于running状态,否则会报ora-23428错误,如果设置为true,则instance可接受任一数字作为job的实例

dbms_job功能上不如dbms_schduler强大,但也相对比较简单,下面是interval常用的examples:

其中trunc(sysdate)+1跟trunc(sysdate+1)是相等的,sysdate表示当前的时间,sysdate+1,这里的单位是天,也就是第二天的当前时间,SYSDATE+1/24则表示当前时间的下一个小时,1天24小时,所以1/24表示一个小时,同理1/24*60则表示1分钟等等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
VARIABLE  jobno   NUMBER;
VARIABLE  instno  NUMBER;
BEGIN
    select instance_number into :instno from v$instance;
    -- ------------------------------------------------------------
    -- Submit job to begin at 0600 and run every hour.
    -- ------------------------------------------------------------
    DBMS_JOB.SUBMIT (
        :jobno
      , 'statspack.snap;'
      , TRUNC(sysdate+1)+6/24
      , 'TRUNC(SYSDATE+1/24,''HH'')'
      , TRUE
      , :instno);
    -- ------------------------------------------------------------
    -- Submit job to begin at 0900 and run 12 hours later
    -- ------------------------------------------------------------
    DBMS_JOB.SUBMIT (
        :jobno
      , 'statspack.snap;'
      , TRUNC(sysdate+1)+9/24
      , 'TRUNC(SYSDATE+12/24,''HH'')'
      , TRUE
      , :instno);
    -- ------------------------------------------------------------
    -- Submit job to begin at 0600 and run every 10 minutes
    -- ------------------------------------------------------------
    DBMS_JOB.SUBMIT (
        :jobno
      , 'statspack.snap;'
      , TRUNC(sysdate+1)+6/24
      , 'TRUNC(sysdate+10/1440,''MI'')'
      , TRUE
      , :instno);
    -- ----------------------------------------------------------------
    -- Submit job to begin at 0600 and run every hour, Monday - Friday
    -- ----------------------------------------------------------------
    DBMS_JOB.SUBMIT (
        :jobno
      , 'statspack.snap;'
      , TRUNC(sysdate+1)+6/24
      , 'TRUNC(
             LEAST(
                 NEXT_DAY(sysdate,''MONDAY'')
               , NEXT_DAY(sysdate,''TUESDAY'')
               , NEXT_DAY(sysdate,''WEDNESDAY'')
               , NEXT_DAY(sysdate,''THURSDAY'')
               , NEXT_DAY(sysdate,''FRIDAY'')
             ) + 1/24
        , ''HH'')'
      , TRUE
      , :instno);
    COMMIT;
END;
/

从上面的例子我们可以看出,对于这样的需求从早上8点开始到下午17点结束,dbms_job就不如dbms_scheduler直观,

这里需要使用CASE子句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--oracle9i,每天早上8:00am到下午5:00pm这段高峰期,每隔一个小时生成一次snapshot
VARIABLE id_job number;
VARIABLE inst_num number;
begin
    SELECT instance_number INTO :inst_num FROM v$instance;
	dbms_job.submit(
		job => id_job, -- OUT; 
		what => 'statspack.snap;', 
        next_date => TRUNC(SYSDATE+1/24)
        inverval => 'case when to_char(SYSDATE, ''hh24:mi'') between ''08:00'' and ''17:00'' then TRUNC(SYSDATE+1/24,''HH'') else trunc(SYSDATE+1)+(8/24) end'
        instance => :inst_num
      );
commit;
end;
/

DBMS_JOB其它相关函数

1
2
3
4
--dbms_job.run(job_id): job_id为dbms_job.submit()函数返回的job id号
--dbms_job.remove(job_id): 移除job
--dbms_job.change(...): 修改一个已经存在的job的所有属性
--dbms_job.what(job_id,what对应的命令):只修改指定job中执行的命令

参考文章:

转载请注明原作者: 周淑科(https://izsk.me)

wechat
Scan Me To Read on Phone
I know you won't do this,but what if you did?