如何查询特定Oracle作业最后一次运行的状态？

问题背景 #

使用dbms_job.submit提交调用存储过程的作业后，遇到以下问题：

• DBA_JOBS视图仅显示运行中的作业，其中WHAT字段可明确标识作业执行内容，但作业完成后就会从该视图中消失
• ALL_SCHEDULER_JOB_RUN_DETAILS视图记录了作业运行历史，但缺少类似WHAT的可识别字段，无法直接与DBA_JOBS关联

需要编写SQL查询这类作业的最后一次运行状态。

相关代码 #

创建存储过程

CREATE OR REPLACE PROCEDURE USER1.fake_do_mv_refresh (
        p_sleep_secs int,
        p_user    VARCHAR2,
        p_cm_r_pm VARCHAR2 DEFAULT 'CM'
    )
AS
fake_error EXCEPTION;
BEGIN
  DBMS_LOCK.SLEEP(p_sleep_secs);
    raise fake_error;
    --INSERT INTO FDS_APPS.MSG VALUES (p_cm_r_pm, current_timestamp);
    --commit;
END fake_do_mv_refresh;
/

提交作业的PL/SQL块

DECLARE
l_jobid int := -1;
l_what varchar2(1000) := 'ok';
p_user varchar(10) := 'USER1';
p_cm_r_pm varchar(2) := 'CM';
p_sleep_secs int := 60;
BEGIN
  
    l_what :=  'USER1.fake_Do_mv_refresh(' || p_sleep_secs || ',''' || p_user  || ''',''' || p_cm_r_pm || ''');';    
    dbms_output.put_line(l_what);

    dbms_job.submit(job => l_jobid, what => l_what);
    dbms_output.put_line(l_jobid);
 
    COMMIT;
 
END;
/

尝试过的查询

-- 仅显示运行中的作业
select * FROM DBA_SCHEDULER_RUNNING_JOBS;

-- 仅显示运行中的作业，WHAT字段可识别作业
select * FROM DBA_JOBS;

-- 显示作业运行历史，但无明确识别字段
select * FROM all_scheduler_job_run_details order by log_date desc;

解决方案 #

dbms_job创建的作业在调度器视图中对应的JOB_NAME格式为JOB$<作业ID>，可以通过这个规则关联DBA_JOBS和DBA_SCHEDULER_JOB_RUN_DETAILS视图，获取作业的历史运行状态。

方式1：通过作业执行内容关联查询

SELECT
    dj.job AS 作业ID,
    dj.what AS 作业执行内容,
    sjrd.log_date AS 运行时间,
    sjrd.status AS 运行状态,
    sjrd.error# AS 错误代码,
    sjrd.run_duration AS 运行时长
FROM
    DBA_JOBS dj
RIGHT JOIN
    DBA_SCHEDULER_JOB_RUN_DETAILS sjrd
ON
    sjrd.job_name = 'JOB$' || dj.job
WHERE
    -- 替换为你要查询的作业特征内容
    dj.what LIKE '%USER1.fake_do_mv_refresh%'
ORDER BY
    sjrd.log_date DESC
FETCH FIRST 1 ROW ONLY;

方式2：直接通过作业ID查询（作业已不在DBA_JOBS时可用）

SELECT
    log_date AS 运行时间,
    status AS 运行状态,
    error# AS 错误代码,
    run_duration AS 运行时长,
    job_name AS 调度器作业名
FROM
    DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
    job_name = 'JOB$<你的作业ID>' -- 替换为实际作业ID
ORDER BY
    log_date DESC
FETCH FIRST 1 ROW ONLY;

字段说明 #

• status常见取值：SUCCEEDED（成功）、FAILED（失败）、STOPPED（停止）
• error#记录失败时的错误代码，可结合SQLERRM(error#)查看具体错误信息
• run_duration表示作业运行的时长

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