Oracle调度作业执行信息查询及相关技术问题咨询

1. 查询作业的上次运行时间、运行时长及执行结果 #

可以通过查询USER_SCHEDULER_JOB_RUN_DETAILS（当前用户权限内的作业）或DBA_SCHEDULER_JOB_RUN_DETAILS（需DBA权限，查看所有用户作业）视图获取这些信息，示例SQL如下：

SELECT
  actual_start_date AS 上次运行开始时间,
  run_duration AS 运行时长,
  status AS 执行结果,
  log_date AS 日志生成时间
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name = 'MY_ABC_JOB'
ORDER BY actual_start_date DESC
FETCH FIRST 1 ROW ONLY;

• actual_start_date：作业实际启动的时间（比log_date更精准，后者是日志记录时间）
• run_duration：作业运行时长，格式为HH24:MI:SS.FF
• status：执行结果，常见取值为SUCCEEDED（成功）、FAILED（失败）、STOPPED（已终止）

若需查看所有历史运行记录，移除FETCH FIRST 1 ROW ONLY即可。

2. 获取作业执行时受影响的行数及DBMS输出 #

受影响行数 #

Oracle调度作业不会自动记录SQL受影响行数，需在MY_ABC_PROC存储过程中主动捕获并存储：

1. 用SQL%ROWCOUNT获取DML语句的受影响行数
2. 将行数写入自定义日志表，或通过DBMS_SCHEDULER.ADD_JOB_RUN_DETAIL追加到作业运行详情中

示例（写入自定义日志表）：

CREATE OR REPLACE PROCEDURE MY_ABC_PROC(p_param IN NUMBER) IS
  v_affected_rows NUMBER;
BEGIN
  -- 示例DML操作
  UPDATE your_table SET col_status = 'PROCESSED' WHERE col_param = p_param;
  v_affected_rows := SQL%ROWCOUNT;

  -- 写入日志表（需提前创建：CREATE TABLE JOB_RUN_LOG(job_name VARCHAR2(100), run_time DATE, affected_rows NUMBER);）
  INSERT INTO JOB_RUN_LOG(job_name, run_time, affected_rows)
  VALUES('MY_ABC_JOB', SYSDATE, v_affected_rows);

  -- 其他业务逻辑
  ...
END;
/

DBMS输出收集 #

默认调度作业不会捕获DBMS_OUTPUT内容，可通过两种方式实现：

• 方式1：开启作业全量日志
  修改作业的LOGGING_LEVEL属性为DBMS_SCHEDULER.LOGGING_FULL，DBMS输出会被记录到ADDITIONAL_INFO字段：

  BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
      job_name => 'MY_ABC_JOB',
      attribute => 'LOGGING_LEVEL',
      value => DBMS_SCHEDULER.LOGGING_FULL
    );
  END;
  /
  

  查询时解析该字段：

  SELECT
    actual_start_date,
    JSON_VALUE(additional_info, '$.dbms_output') AS dbms_output_content
  FROM USER_SCHEDULER_JOB_RUN_DETAILS
  WHERE job_name = 'MY_ABC_JOB'
  ORDER BY actual_start_date DESC;
  

• 方式2：存储过程内写入日志表
  直接将DBMS_OUTPUT内容写入自定义表，灵活性更高：

  CREATE OR REPLACE PROCEDURE MY_ABC_PROC(p_param IN NUMBER) IS
    v_log_content VARCHAR2(4000);
  BEGIN
    v_log_content := '作业启动，参数值：' || p_param;
    DBMS_OUTPUT.PUT_LINE(v_log_content);
    -- 写入日志表（需提前创建：CREATE TABLE JOB_DBMS_LOG(job_name VARCHAR2(100), run_time DATE, log_content VARCHAR2(4000));）
    INSERT INTO JOB_DBMS_LOG(job_name, run_time, log_content)
    VALUES('MY_ABC_JOB', SYSDATE, v_log_content);
  
    -- 其他业务逻辑
    ...
  END;
  /
  

3. 在作业中运行DDL语句的其他方法 #

PL/SQL块（包括调度作业的PLSQL_BLOCK类型）无法直接执行DDL，必须通过动态SQL或调整作业类型实现，除了EXECUTE IMMEDIATE，还有以下方式：

方法1：使用DBMS_SQL包 #

BEGIN
  DECLARE
    v_cursor_id NUMBER;
    v_exec_result NUMBER;
  BEGIN
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, 'ANALYZE TABLE your_table COMPUTE STATISTICS', DBMS_SQL.NATIVE);
    v_exec_result := DBMS_SQL.EXECUTE(v_cursor_id);
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
  END;
END;
/

方法2：修改作业类型为EXECUTABLE或SQL_FILE #

• EXECUTABLE类型：调用操作系统脚本（如Shell脚本），通过SQL*Plus执行DDL，需配置操作系统权限和脚本路径
• SQL_FILE类型：指定包含DDL的SQL文件路径（需数据库能访问该文件，如UTL_FILE目录），示例：

  BEGIN
    DBMS_SCHEDULER.DROP_JOB('MY_ABC_JOB'); -- 先删除原有作业
    DBMS_SCHEDULER.CREATE_JOB(
      job_name        => 'MY_ABC_JOB',
      job_type        => 'SQL_FILE',
      job_action      => '/opt/oracle/scripts/analyze_table.sql', -- 替换为实际路径
      start_date      => SYSDATE,
      repeat_interval => 'FREQ=DAILY; BYHOUR=8,12,16',
      enabled         => TRUE
    );
  END;
  /
  

该方法需额外权限（如CREATE EXTERNAL JOB），且灵活性不如动态SQL。

内容的提问来源于stack exchange，提问作者Leszek Pachura