虚谷数据库定时任务的dbms_scheduler使用方法

虚谷数据库提供定时作业机制，用于定时、定期、自动的进行某些操作，可通过系统包 dbms_scheduler 进行定时作业创建、调度、查看、删除等。

DBMS_SCHEDULER 系统包封装了以下过程/函数：ENABLE、DISABLE、SET_JOB_ARGUMENT_VALUE、DROP_JOB、RUN_JOB。

  • DBMS_SCHEDULER.CREATE_JOB：创建作业
  • DBMS_SCHEDULER.ENABLE：表示启用作业。
  • DBMS_SCHEDULER.DISABLE：表示禁用作业。
  • DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE：表示对 job 调用存储过程进行参数赋值
  • DBMS_SCHEDULER.RUN_JOB：表示显式调用定时作业。
  • DBMS_SCHEDULER.DROP_JOB：表示删除定时作业。

 1、DBMS_SCHEDULER.CREATE_JOB使用方法

DBMS_SCHEDULER.CREATE_JOB (

job_name IN VARCHAR2,

job_type IN VARCHAR2, job_action IN VARCHAR2,

number_of_arguments IN PLS_INTEGER DEFAULT 0,

start_date IN TIMESTAMP DEFAULT NULL,

repeat_interval IN VARCHAR2 DEFAULT NULL,

end_date IN TIMESTAMP DEFAULT NULL,

job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',

enabled IN BOOLEAN DEFAULT FALSE,

auto_drop IN BOOLEAN DEFAULT TRUE,

comments IN VARCHAR2 DEFAULT NULL);

参数解释：

  • job_name：作业名称。
  • job_type：作业类型，可指定为 stored_procedure、plsql_block或plsql_command；若为 stored_procedure 则 job_action 内容为数据库存储过程名称；若为 plsql_block 则 job_action 为可执行块语句；若为plsql_command则job_action可调用包中的存储过程或存储函数。
  • job_action：作业动作，与 job_type 相关。
  • number_of_arguments：作业中存储过程、存储函数或包的参数个数。
  • start_date：作业开始时间。
  • repeat_interval：作业重复间隔说明。
  • end_date：作业结束时间。
  • job_class：作业类型——该参数暂时无效，预留。
  • enabled：作业是否已激活，若该参数置为true则表示该作业默认为启用状态，作业根据其计划自动运行，若该参数置为false则该作业为禁用状态，不会自动执行，需手动执行作业或将该作业启用后方可自动执行。
  • auto_drop：作业执行完是否自动删除。若该参数置为true则在作业完成后将自动删除作业，反之不会删除该作业。
  • comments：作业备注说明。

参数说明：

  • repeat_interval 结构为 REPEAT_INTERVAL=>’Freq=Minutely;Interval=5’ Freq 关键字用于指定作业间隔的时间周期，可选参数包括：YEARLY(年)、MONTHLY(月)、WEEKLY(周)、DAILY(日)、HOURLY(小时)、MINUTELY(分)、SECONDLY(秒)。

  • Interval 关键字用于指定作业间隔频度，该值为一个整数，默认为 1，可指定范围为 1-999。

  • repeat_interval 参数除了以上两个限制条件外，还可指定：

    • BYHOUR：指示定时作业在指定小时执行，可指定范围 0-23，若要指定多个时间参数使用逗号进行分割，如 BYHOUR=2,5,7。
    • BYDAY：指示在每周的第几天运行，可使用数字或英文缩写，如：MON|TUE|WED|THU|FRI|SAT|SUN 等。
    • BYMONTHDAY：指示在每月的第几天运行。
    • BYMONTH：指示在每年的月份，可使用数字或英文缩写，如：JAN|FEB|MAR|APR|MAY|JUN 等。

下面的样例是基于存储过程每隔三十秒执行一次的定时任务

EXEC dbms_scheduler.create_job(
  'job1',
  'stored_procedure',
  'JOB_PROC1',
  2,
  sysdate,
  'freq=secondly ; INTERVAL=30;',
  '2029-01-01 01:00:00',
  'default_class',
  TRUE,
  TRUE,
  '这是一个测试'); 

2、DBMS_SCHEDULER.ENABLE，DBMS_SCHEDULER.DISABLE 使用方法：

DBMS_SCHEDULER.ENABLE ( name IN VARCHAR2); 

DBMS_SCHEDULER.DISABLE ( name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);

参数解释：

  • name：禁用或启用的作业名称。
  • force：如果设置为true则需先停止正在运行的作业实例，再禁用作业；如果设置为false则允许正在运行的作业执行完成，然后再禁用作业。

如果在创建任务的时候参数enabled 选择了true,则不需要执行该命令，查看是否启动可取系统表验证该任务处于启用还是禁用。

SELECT
    t.db_name,
    j.schema_name,
    s.job_name,s.ENABLE
FROM
    dba_jobs s ,
    dba_databases t,
    dba_schemas j
WHERE
    s.db_id = t.db_id
    AND s.user_id = j.schema_id
    AND s.job_name = '作业名'
    AND t.db_name = '库名'
    AND j.schema_name = '模式名'；

3、DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE 使用方法

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2,

argument_position IN PLS_INTEGER,

argument_value IN VARCHAR2);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2,

argument_name IN VARCHAR2,

argument_value IN VARCHAR2);

参数解释：

  • job_name：需要设置参数的作业名称。
  • argument_position：需要设置参数值的参数位置。
  • argument_name：需要设置参数值的参数名称。
  • argument_value：设置的参数值，该处根据参数值类型自动选择重载的存储过程。

该任务是单个参数的情况：

exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job1'--作业名

,'INPUT'--参数名

,7000--参数值

);

或者：

exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job1',

1,--参数位置

7000--参数值

);

该任务是多个参数的情况：

exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job1'--作业名

,'INPUT'--参数名

,7000--参数值

);
 exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job1',--作业名

'dat',--参数名

sysdate--参数值

);

或者：

exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job1',--作业名

1,--参数位置

7000--参数值

);


exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job1',--作业名

2,--参数位置

sysdate--参数值

);

PS:如果参数值需要重新执行DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE命令定义参数值，如果需要立刻启动任务则执行DBMS_SCHEDULER.RUN_JOB，否则它会在下一个周期生效。

4、DBMS_SCHEDULER.RUN_JOB使用方法 

DBMS_SCHEDULER.RUN_JOB ( job_name IN VARCHAR2, use_current_session IN BOOLEAN DEFAULT TRUE);

参数解释：

  • job_name：调用的作业名称。
  • use_current_session：参数选用true时表示使用当前会话执行该作业，若作业未执行完成则当前连接会话一直处于阻塞状态直至作业完成后方可执行其他SQL；若参数选用false则表示使用其他会话执行该作业，当前会话可继续执行其他SQL。

 样例：

exec DBMS_SCHEDULER.RUN_JOB ( 'job_name');

5、DBMS_SCHEDULER.DROP_JOB使用方法

DBMS_SCHEDULER.DROP_JOB ( job_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);

参数解释：

  • job_name：删除的作业名称。
  • force：如果设置为true则需先停止正在运行的作业实例，再删除作业；如果设置为false则允许正在运行的作业执行完成，然后再删除这些作业。

样例：

 exec DBMS_SCHEDULER.DROP_JOB ( 'job_name');

6、系统表查询：

SELECT
    t.db_name,
    j.schema_name,
    s.*
FROM
    dba_jobs s ,
    dba_databases t,
    dba_schemas j
WHERE
    s.db_id = t.db_id
    AND s.user_id = j.schema_id
    AND s.job_name = '作业名'
    AND t.db_name = '库名'
    AND j.schema_name = '模式名'；