如何监控Oracle调度作业执行时长并触发超时记录插入？

方案一：存储过程内嵌超时监控（最简便，适配dbms_job） #

在主存储过程中启动独立监控任务，定期检查主作业运行时长，超时则插入记录，无需频繁轮询作业表。

步骤1：创建监控存储过程 #

CREATE OR REPLACE PROCEDURE monitor_job_timeout(p_job_id NUMBER, p_target_table VARCHAR2) IS
    v_start_time DATE;
    v_elapsed_minutes NUMBER;
BEGIN
    -- 获取目标作业的启动时间
    SELECT start_date INTO v_start_time FROM user_jobs WHERE job = p_job_id;
    
    LOOP
        v_elapsed_minutes := (SYSDATE - v_start_time) * 24 * 60;
        -- 检测到超时则插入记录并退出
        IF v_elapsed_minutes >= 60 THEN
            EXECUTE IMMEDIATE 'INSERT INTO ' || p_target_table || ' (job_id, timeout_time, status) VALUES (:1, :2, ''TIMEOUT'')'
                USING p_job_id, SYSDATE;
            COMMIT;
            EXIT;
        END IF;
        -- 每5分钟检查一次，降低查询频率
        DBMS_LOCK.SLEEP(300);
        -- 主作业已完成则终止监控
        IF NOT EXISTS (SELECT 1 FROM user_jobs WHERE job = p_job_id AND what IS NOT NULL) THEN
            EXIT;
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        EXIT;
END;
/

步骤2：在主存储过程中调用监控任务 #

CREATE OR REPLACE PROCEDURE your_main_procedure IS
    v_monitor_job_id NUMBER;
    v_current_job_id NUMBER;
BEGIN
    -- 获取当前dbms_job的ID
    SELECT job INTO v_current_job_id FROM user_jobs WHERE sid = SYS_CONTEXT('USERENV', 'SID');
    
    -- 提交一次性监控任务
    DBMS_JOB.SUBMIT(
        job => v_monitor_job_id,
        what => 'monitor_job_timeout(' || v_current_job_id || ', ''your_target_table'');',
        next_date => SYSDATE,
        interval => NULL
    );
    COMMIT;

    -- 你的核心业务逻辑代码
    -- ...

EXCEPTION
    WHEN OTHERS THEN
        -- 异常时清理监控任务
        DBMS_JOB.REMOVE(v_monitor_job_id);
        COMMIT;
        RAISE;
END;
/

方案二：用DBMS_SCHEDULER事件触发（推荐，原生监听模式） #

如果可以替换dbms_job为Oracle官方推荐的DBMS_SCHEDULER，可直接利用其内置超时事件机制，完全无需手动查询作业表，符合你想要的“事件监听器”模式。

步骤1：创建带超时阈值的作业类 #

BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS(
        job_class_name => 'long_running_jobs',
        resource_consumer_group => 'DEFAULT_CONSUMER_GROUP',
        logging_level => DBMS_SCHEDULER.LOGGING_FULL,
        auto_drop => FALSE
    );
    -- 设置作业最大运行时长为60分钟
    DBMS_SCHEDULER.SET_ATTRIBUTE('long_running_jobs', 'MAX_RUN_DURATION', INTERVAL '60' MINUTE);
END;
/

步骤2：创建超时事件处理过程 #

CREATE OR REPLACE PROCEDURE handle_job_timeout(p_event IN SYS.SCHEDULER_EVENT_INFO) IS
BEGIN
    INSERT INTO your_target_table (job_name, timeout_time, status)
    VALUES (p_event.job_name, SYSDATE, 'TIMEOUT');
    COMMIT;
END;
/

步骤3：创建事件监听作业 #

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'job_timeout_listener',
        job_type => 'STORED_PROCEDURE',
        job_action => 'handle_job_timeout',
        event_condition => 'event_type = ''JOB_OVER_MAX_DUR''',
        queue_spec => 'SYS.SCHEDULER$_EVENT_QUEUE',
        enabled => TRUE,
        auto_drop => FALSE
    );
END;
/

步骤4：创建关联作业类的主调度作业 #

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'your_main_job',
        job_type => 'STORED_PROCEDURE',
        job_action => 'your_main_procedure',
        start_date => SYSDATE,
        repeat_interval => 'FREQ=DAILY;BYHOUR=2', -- 按需设置调度周期
        job_class => 'long_running_jobs',
        enabled => TRUE
    );
END;
/

关键注意事项 #

• 方案一中需确保监控任务在主作业完成/异常时被清理，避免残留僵尸任务。
• DBMS_SCHEDULER支持Oracle 10g及以上版本，功能远强于旧版dbms_job，官方建议优先使用。
• 执行上述操作需具备对应权限：DBMS_JOB/DBMS_SCHEDULER/DBMS_LOCK执行权限，以及目标表的插入权限。

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