Oracle 自动发送邮件监控 ETL 执行状态（一）

整体架构

通过 Shell 脚本定时调用 SQL*Plus 执行 etl_monitor.sql，后者查询 USER_SCHEDULER_JOB_RUN_DETAILS 视图判断 ETL 作业是否成功，并统计目标表前日数据量，最后调用 PROCSENDEMAIL 发送监控报告。

Shell 脚本（etl_monitor.sh）

#!/bin/bash
ORACLE_SID=orcl
ORACLE_BASE=/opt/Oracle
ORACLE_HOME=/opt/Oracle/product/10.2.0
export ORACLE_SID ORACLE_BASE ORACLE_HOME

PWD_DIR=/home/Oracle/shell
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
CONFIG_INI=${PWD_DIR}/ini/config.ini

while read GAMEUSER
do
    echo ${GAMEUSER}
    cd ${PWD_DIR}
    ${SQLPLUS} ${GAMEUSER} << !
@etl_monitor.sql;
/
exit;
!
done < ${CONFIG_INI}

监控 SQL 核心逻辑（etl_monitor.sql）

DECLARE
    P_TXT      VARCHAR2(4000);
    P_TXT_ALL  VARCHAR2(4000);
BEGIN
    FOR R IN (SELECT JOB_NAME, RUN_CNT, TABLE_NAME, COLUMN_NAME
              FROM ETL_MONITOR_CONFIG_TAB)
    LOOP
        -- 调用监控函数
        P_TXT := ETL_MONITOR(R.JOB_NAME, R.RUN_CNT, R.TABLE_NAME, R.COLUMN_NAME);
        P_TXT_ALL := P_TXT_ALL || CHR(13) || P_TXT;
    END LOOP;

    -- 发送汇总邮件
    PROCSENDEMAIL(
        P_TXT_ALL,
        'ETL Monitor',
        'xxx@163.com',
        'xxxx@kingsoft.com',
        'mail.kingsoft.com',
        25,
        1,
        'xxxxxx',
        'xxxxxx',
        '',
        'bit 7'
    );
    P_TXT_ALL := '';
END;
/

ETL_MONITOR 函数关键逻辑

1. 根据 RUN_CNT 决定取当日首次或最新一次作业运行记录（LOG_ID）。
2. 检查 STATUS = 'SUCCEEDED' 判断作业本身是否成功。
3. 动态 SQL 查询目标表 WHERE TRUNC(COLUMN_NAME) = TRUNC(SYSDATE-1) 且 ROWNUM=1，确认前日有数据产出。
4. 双重条件均满足返回 SUCCESS，否则返回失败详情（含 ADDITIONAL_INFO）。
5. 异常捕获返回 NOT EXECUTE。

部署要点

• CONFIG_INI 每行一个连接字符串，便于多环境/多租户监控。
• 需授予监控用户 SELECT ON USER_SCHEDULER_JOB_RUN_DETAILS 权限。
• SMTP 服务器需允许数据库服务器 IP 直连 25 端口。