Oracle跨DBLINK临时表空间监控未触发告警，核心查询是否正确？

数据老陈

2026-6-19

问题

基于Oracle的DBLINK开发了临时表空间监控存储过程SP_TEMP_MON，当任意临时表空间使用率达到参数v_used指定的百分比时发送告警邮件。已设置定时任务每5分钟执行该存储过程，将v_used设为85%，但临时表空间已满时并未触发邮件告警。现咨询：核心查询语句是否存在错误？或者监控方案存在遗漏？

存储过程代码如下：

CREATE OR REPLACE PROCEDURE SP_TEMP_MON(
    v_used NUMBER
) AS
    v_sql                        VARCHAR2(4000);
    v_html                       CLOB := EMPTY_CLOB();
    v_execution_date             DATE := SYSDATE;
    v_db_link_name               VARCHAR2(128);
    v_link_open                  BOOLEAN := FALSE;
    v_actions_found              BOOLEAN := FALSE;
    v_total_mb                   NUMBER;
    v_query_mb_used              NUMBER;
    v_tablespace_mb_used         NUMBER;
    v_query_percentage_used      NUMBER;
    v_tablespace_percentage_used NUMBER;

    TYPE temp_details_type IS RECORD (
        tablespace         VARCHAR2(128),
        os_username        VARCHAR2(128),
        sql_text           VARCHAR2(4000),
        query_mb_used      NUMBER,
        tablespace_mb_used NUMBER,
        total_mb           NUMBER
    );
    TYPE temp_details_table IS TABLE OF temp_details_type;

    temp_details_list temp_details_table := temp_details_table();

    CURSOR c_dblinks IS
        SELECT NOMBREDBLINK || '.DBLINK.DOMAIN.COM' AS NOMBREDBLINK
        FROM BDHIST.CATALOGO_DBLINKS
        WHERE CONECTA = 'SI' AND NODO IS NULL;

BEGIN
    -- 遍历数据库中所有需要审计的用户DBLINK
    FOR rec_link IN c_dblinks LOOP
        v_db_link_name := rec_link.NOMBREDBLINK;
        v_link_open := FALSE;

        BEGIN
            -- 构建获取表空间使用信息的动态查询
            v_sql := 'WITH sort_usage AS (
                            SELECT 
                                T.tablespace,
                                SUM(T.blocks * TBS.block_size) / 1024 / 1024 AS mb_used,
                                S.osuser,
                                Q.sql_text
                            FROM 
                                v$sort_usage@''||v_db_link_name||'' T
                            JOIN 
                                v$session@''||v_db_link_name||'' S ON T.session_addr = S.saddr
                            LEFT JOIN 
                                v$sqlarea@''||v_db_link_name||'' Q ON T.sqladdr = Q.address
                            JOIN 
                                dba_tablespaces@''||v_db_link_name||'' TBS ON T.tablespace = TBS.tablespace_name
                            GROUP BY 
                                T.tablespace, S.osuser, Q.sql_text
                        ),
                        tablespace_summary AS (
                            SELECT   
                                A.tablespace_name AS tablespace,
                                SUM(A.used_blocks * D.block_size) / 1024 / 1024 AS mb_used,
                                SUM(D.mb_total) AS total_mb
                            FROM
                                v$sort_segment@''||v_db_link_name||'' A
                            JOIN
                                (SELECT
                                    B.name,
                                    C.block_size,
                                    SUM(C.bytes) / 1024 / 1024 AS mb_total
                                FROM
                                    v$tablespace@''||v_db_link_name||'' B
                                JOIN
                                    v$tempfile@''||v_db_link_name||'' C ON B.ts# = C.ts#
                                GROUP BY 
                                    B.name,
                                    C.block_size) D ON A.tablespace_name = D.name
                            GROUP BY 
                                A.tablespace_name
                        )
                        SELECT 
                            ts.tablespace,
                            su.osuser,
                            su.sql_text,
                            su.mb_used AS query_mb_used,
                            ts.mb_used AS tablespace_mb_used,
                            ts.total_mb
                        FROM 
                            tablespace_summary ts
                        JOIN 
                            sort_usage su ON ts.tablespace = su.tablespace
                        ORDER BY 
                            query_mb_used DESC';

            EXECUTE IMMEDIATE v_sql BULK COLLECT INTO temp_details_list;
            v_link_open := TRUE;

           -- 如果找到数据则打印表头
            IF temp_details_list.COUNT > 0 THEN
                -- 检查使用率是否满足邮件发送条件
                v_actions_found := FALSE;
                       
                FOR i IN 1..temp_details_list.COUNT LOOP
                    v_total_mb := temp_details_list(i).total_mb;
                    v_tablespace_mb_used := temp_details_list(i).tablespace_mb_used;
                    v_tablespace_percentage_used := (v_tablespace_mb_used / v_total_mb) * 100;

                    IF v_tablespace_percentage_used >= v_used OR (v_total_mb - v_tablespace_mb_used) / v_total_mb * 100 <= 100 - v_used THEN
                        v_actions_found := TRUE;
                        EXIT;
                    END IF;
                END LOOP;

                -- HTML表头
                v_html :=
                    v_html
                    || '<h2>DBLINK: ' || v_db_link_name || '</h2>'
                    || '<table border="1" cellpadding="5" cellspacing="0">'
                    || '<tr>'
                    || '<th>TABLESPACE</th>'
                    || '<th>OS_USERNAME</th>'
                    || '<th>SQL_TEXT</th>'
                    || '<th>QUERY_MB_USED</th>'
                    || '<th>TOTAL_MB</th>'
                    || '<th>QUERY_PERCENTAGE_USED</th>'
                    || '<th>TABLESPACE_PERCENTAGE_USED</th>'
                    || '</tr>';

                FOR i IN 1..temp_details_list.COUNT LOOP
                    v_query_mb_used := temp_details_list(i).query_mb_used;
                    v_query_percentage_used := (v_query_mb_used / v_total_mb) * 100;
                    v_html :=
                        v_html
                        || '<tr>'
                        || '<td>' || temp_details_list(i).tablespace || '</td>'
                        || '<td>' || temp_details_list(i).os_username || '</td>'
                        || '<td>' || temp_details_list(i).sql_text || '</td>'
                        || '<td>' || TO_CHAR(temp_details_list(i).query_mb_used) || '</td>'
                        || '<td>' || TO_CHAR(temp_details_list(i).total_mb) || '</td>'
                        || '<td>' || TO_CHAR(v_query_percentage_used, 'FM9999990.000') || '%</td>'
                        || '<td>' || TO_CHAR(v_tablespace_percentage_used, 'FM9999990.000') || '%</td>'
                        || '</tr>';
                END LOOP;

                v_html := v_html || '</table>';
            ELSE
                v_html :=
                    v_html
                    || '<h2>DBLINK详情: ' || v_db_link_name || '</h2>'
                    || '<p>临时表空间未被使用。</p>';
            END IF;

            COMMIT;

            IF v_link_open THEN
                EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK('''||v_db_link_name||'''); END;';
                v_link_open := FALSE;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                IF v_link_open THEN
                    EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK('''||v_db_link_name||'''); END;';
                END IF;

                DBMS_OUTPUT.PUT_LINE('处理DBLINK ' || v_db_link_name || '时出错: ' || SQLERRM);
        END;
    END LOOP;

   IF v_actions_found THEN
        MONITORING_SCHEMA.PG_ENVIO_MAIL.entrega(
            vg_from      => 'sender@mail.com',
            vg_to        => 'receipt@mail.com',
            vg_asunto    => 'TEMP监控告警',
            vg_cuerpo    => v_html,
            vg_firma     => 'sender',
            vg_cc        => 'cc@mail.com'
        );
    END IF;

EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
/
SHOW ERRORS;

核心查询语句如下：

WITH sort_usage AS (
                            SELECT 
                                T.tablespace,
                                SUM(T.blocks * TBS.block_size) / 1024 / 1024 AS mb_used,
                                S.osuser,
                                Q.sql_text
                            FROM 
                                v$sort_usage@''||v_db_link_name||'' T
                            JOIN 
                                v$session@''||v_db_link_name||'' S ON T.session_addr = S.saddr
                            LEFT JOIN 
                                v$sqlarea@''||v_db_link_name||'' Q ON T.sqladdr = Q.address
                            JOIN 
                                dba_tablespaces@''||v_db_link_name||'' TBS ON T.tablespace = TBS.tablespace_name
                            GROUP BY 
                                T.tablespace, S.osuser, Q.sql_text
                        ),
                        tablespace_summary AS (
                            SELECT   
                                A.tablespace_name AS tablespace,
                                SUM(A.used_blocks * D.block_size) / 1024 / 1024 AS mb_used,
                                SUM(D.mb_total) AS total_mb
                            FROM
                                v$sort_segment@''||v_db_link_name||'' A
                            JOIN
                                (SELECT
                                    B.name,
                                    C.block_size,
                                    SUM(C.bytes) / 1024 / 1024 AS mb_total
                                FROM
                                    v$tablespace@''||v_db_link_name||'' B
                                JOIN
                                    v$tempfile@''||v_db_link_name||'' C ON B.ts# = C.ts#
                                GROUP BY 
                                    B.name,
                                    C.block_size) D ON A.tablespace_name = D.name
                            GROUP BY 
                                A.tablespace_name
                        )
                        SELECT 
                            ts.tablespace,
                            su.osuser,
                            su.sql_text,
                            su.mb_used AS query_mb_used,
                            ts.mb_used AS tablespace_mb_used,
                            ts.total_mb
                        FROM 
                            tablespace_summary ts
                        JOIN 
                            sort_usage su ON ts.tablespace = su.tablespace
                        ORDER BY 
                            query_mb_used DESC

问题分析与修正

一、核心查询语句的错误

1. 动态SQL拼接错误

原代码中DBLINK的拼接格式@''||v_db_link_name||''会生成无效的DBLINK引用（比如v$sort_usage@''MYLINK''），正确的拼接应该是@'||v_db_link_name||'，去掉多余的单引号，确保解析后为v$sort_usage@MYLINK。

2. 临时表空间使用率统计不完整

v$sort_segment仅统计当前活跃的排序段，无法覆盖异常会话遗留的未释放临时段，导致计算的使用率低于实际值。
使用JOIN sort_usage su会过滤掉无活跃排序操作的临时表空间，即使这些表空间已满，也不会进入告警判断逻辑。

3. 冗余的告警条件

v_tablespace_percentage_used >= v_used和(v_total_mb - v_tablespace_mb_used)/v_total_mb*100 <= 100 - v_used是完全等价的，重复判断没有意义。

二、监控方案的遗漏

1. 未覆盖无活跃会话但表空间已满的场景

当临时表空间被遗留临时段占满，但没有活跃排序会话时，原查询返回空结果，直接跳过告警。

2. 异常信息无法追踪

定时任务执行时DBMS_OUTPUT的内容无法被捕获，DBLINK连接失败等异常无法及时排查。

3. 告警标记被重置

v_actions_found在遍历每个DBLINK时会被重置为FALSE，如果前一个DBLINK触发告警，后续DBLINK处理时会覆盖标记，导致最终不发送邮件。

三、修正后的关键调整

1. 修正动态SQL拼接

将所有@''||v_db_link_name||''替换为@'||v_db_link_name||'，同时改用更准确的临时表空间统计方式：

v$sql := 'WITH sort_usage AS (
                SELECT 
                    T.tablespace,
                    SUM(T.blocks * TBS.block_size) / 1024 / 1024 AS mb_used,
                    S.osuser,
                    Q.sql_text
                FROM 
                    v$sort_usage@'||v_db_link_name||' T
                JOIN 
                    v$session@'||v_db_link_name||' S ON T.session_addr = S.saddr
                LEFT JOIN 
                    v$sqlarea@'||v_db_link_name||' Q ON T.sqladdr = Q.address
                JOIN 
                    dba_tablespaces@'||v_db_link_name||' TBS ON T.tablespace = TBS.tablespace_name
                GROUP BY 
                    T.tablespace, S.osuser, Q.sql_text
            ),
            tablespace_summary AS (
                SELECT   
                    B.name AS tablespace,
                    SUM(C.bytes_used)/1024/1024 AS mb_used,
                    SUM(C.bytes)/1024/1024 AS total_mb
                FROM
                    v$tablespace@'||v_db_link_name||' B
                JOIN
                    dba_temp_files@'||v_db_link_name||' C ON B.ts# = C.ts#
                GROUP BY 
                    B.name
            )
            SELECT 
                ts.tablespace,
                NVL(su.osuser, ''无活跃会话''),
                NVL(su.sql_text, ''无活跃SQL''),
                NVL(su.mb_used, 0) AS query_mb_used,
                ts.mb_used AS tablespace_mb_used,
                ts.total_mb
            FROM 
                tablespace_summary ts
            LEFT JOIN 
                sort_usage su ON ts.tablespace = su.tablespace
            ORDER BY 
                ts.mb_used DESC';

2. 调整告警逻辑

使用LEFT JOIN确保所有临时表空间都能被统计，即使无活跃排序会话。
避免重置v_actions_found，只要有一个表空间触发告警就保持标记为TRUE：

IF temp_details_list.COUNT > 0 THEN
    FOR i IN 1..temp_details_list.COUNT LOOP
        v_total_mb := temp_details_list(i).total_mb;
        v_tablespace_mb_used := temp_details_list(i).tablespace_mb_used;
        v_tablespace_percentage_used := (v_tablespace_mb_used / v_total_mb) * 100;

        IF v_tablespace_percentage_used >= v_used THEN
            v_actions_found := TRUE;
        END IF;
    END LOOP;
    -- 生成HTML内容...
ELSE
    -- 无法获取数据时标记为告警
    v_actions_found := TRUE;
    v_html := v_html || '<h2>DBLINK: ' || v_db_link_name || '</h2>' || '<p>无法获取临时表空间数据，请检查DBLINK权限或连接状态！</p>';
END IF;

3. 优化异常处理

将异常信息写入日志表，方便后续排查：

EXCEPTION
    WHEN OTHERS THEN
        IF v_link_open THEN
            EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK('''||v_db_link_name||'''); END;';
        END IF;
        -- 写入错误日志表（需提前创建）
        INSERT INTO MONITORING_SCHEMA.TEMP_MON_ERROR_LOG (dblink_name, error_msg, error_date)
        VALUES (v_db_link_name, SQLERRM, SYSDATE);
        COMMIT;

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

Oracle跨DBLINK临时表空间监控未触发告警，核心查询是否正确？

一、核心查询语句的错误 #

1. 动态SQL拼接错误 #

2. 临时表空间使用率统计不完整 #

3. 冗余的告警条件 #

二、监控方案的遗漏 #

1. 未覆盖无活跃会话但表空间已满的场景 #

2. 异常信息无法追踪 #

3. 告警标记被重置 #

三、修正后的关键调整 #

1. 修正动态SQL拼接 #

2. 调整告警逻辑 #

3. 优化异常处理 #