Oracle数据库定时任务dbms_job的用法详解

概述

dbms_job 是 Oracle 数据库中用于创建、管理和调度自动任务（定时任务）的工具。它可以指定任务的执行时间、执行频率和执行内容等参数，是常用的自动化运维工具之一。

创建任务

要创建一个定时任务，可以使用 dbms_job.submit 存储过程。该存储过程的语法如下：

dbms_job.submit(
  job OUT BINARY_INTEGER,
  what IN VARCHAR2,
  next_date IN DATE DEFAULT sysdate,
  interval IN VARCHAR2 DEFAULT 'null',
  no_parse IN BOOLEAN DEFAULT FALSE,
  instance IN BINARY_INTEGER DEFAULT 0,
  force IN BOOLEAN DEFAULT FALSE
);

其中，各个参数的含义如下：

• job: 返回的任务 ID，可以用来查询和修改任务。
• what: 需要执行的 PL/SQL 代码字符串。
• next_date: 任务的下次执行时间，默认为当前时间。
• interval: 任务的执行间隔，默认为 null。该参数可以用来指定任务的执行频率，支持各种时间间隔，例如：'sysdate + 1', 'trunc(sysdate + 1/24)', 'trunc(sysdate, ''HH24'') + 1/24'。
• no_parse: 是否对 what 参数进行语法检查。
• instance: 用于指定集群中的实例 ID。
• force: 是否强制提交任务，即使该任务执行失败或被其他任务锁定。

下面是一个创建任务的示例代码：

DECLARE
  job BINARY_INTEGER;
BEGIN
  dbms_job.submit(job, 'BEGIN dbms_stats.gather_table_stats(''SCHEMA_NAME'', ''TABLE_NAME''); END;', 
                  trunc(sysdate, 'HH24') + 1/24, 'trunc(sysdate, ''HH24'') + 1', TRUE);
  COMMIT;
END;

上述代码中，我们使用了 dbms_stats.gather_table_stats 存储过程来为一张表进行收集统计信息的操作。该任务会在每天凌晨 1 点开始执行，并会以每小时一次的频率自动重复执行。

修改任务

要修改任务的执行内容或者执行间隔，可以使用 dbms_job.change 存储过程。该存储过程的语法如下：

dbms_job.change(
  job IN NUMBER,
  what IN VARCHAR2,
  next_date IN DATE DEFAULT sysdate,
  interval IN VARCHAR2 DEFAULT 'null'
);

其中，各个参数的含义和默认值与 dbms_job.submit 存储过程类似。下面是一个修改任务的示例代码，用于修改上面的收集统计信息的任务执行间隔为 2 小时：

DECLARE
  job_id BINARY_INTEGER;
BEGIN
  SELECT job FROM user_jobs WHERE what LIKE '%SCHEMA_NAME%TABLE_NAME%';
  dbms_job.change(job_id, NULL, NULL, 'trunc(sysdate, ''HH24'') + 2/24');
  COMMIT;
END;

上述代码中，我们首先通过查询 user_jobs 视图的 what 列中是否包含目标表的名称来获取任务 ID。然后，使用 dbms_job.change 存储过程来修改任务的执行间隔为 2 小时。注意，在修改任务时，如果 next_date 或 interval 参数传入 null，则会保持原有值不变。

删除任务

要删除一个定时任务，可以使用 dbms_job.remove 存储过程。该存储过程的语法如下：

dbms_job.remove(
  job IN NUMBER
);

其中，job 参数为需要删除的任务 ID。下面是一个删除任务的示例代码，用于删除上面的收集统计信息的任务：

DECLARE
  job_id BINARY_INTEGER;
BEGIN
  SELECT job FROM user_jobs WHERE what LIKE '%SCHEMA_NAME%TABLE_NAME%';
  dbms_job.remove(job_id);
  COMMIT;
END;

示例说明

假设我们需要每天 7 点到 23 点每小时跑一次收集统计信息的任务，并且需要实时监控任务的状态。

首先，我们可以使用下面的代码来创建收集统计信息的任务：

DECLARE
  job BINARY_INTEGER;
BEGIN
  dbms_job.submit(job, 'BEGIN dbms_stats.gather_table_stats(''MY_SCHEMA'', ''MY_TABLE''); END;',
                  trunc(sysdate, 'HH24') + 7/24, 'trunc(sysdate, ''HH24'') + 1', TRUE);
  COMMIT;
END;

该代码用于创建一个收集统计信息的任务，该任务会在每天早上 7 点开始执行，并以每小时一次的频率自动重复执行。

接着，我们可以使用如下代码来实时监控该任务的状态：

SELECT job, log_date, status, failures FROM user_jobs WHERE job = :job_id;

其中，job_id 参数为任务 ID。以上代码将返回该任务的执行记录，包括任务的执行时间、状态和失败次数等信息。

总结

dbms_job 是 Oracle 数据库中用于创建、管理和调度自动任务（定时任务）的工具。它可以指定任务的执行时间、执行频率和执行内容等参数，是常用的自动化运维工具之一。通过 dbms_job.submit 存储过程，我们可以创建新的任务；通过 dbms_job.change 存储过程，我们可以修改指定任务的属性；通过 dbms_job.remove 存储过程，我们可以删除指定任务。在实际运维过程中，我们可以结合实际需求和业务场景，灵活使用 dbms_job 工具来提高运维效率和自动化程度。