Правильно ли осуществляется мониторинг табличных пространств TEMP?

Я разработал этот монитор табличных пространств TEMP через DBLINKS в ORACLE, он отправляет электронное письмо, если какое-либо временное табличное пространство достигает какого-либо процента, вызванного v_used:

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
    -- We iterate through all the user DBLINKs defined in the database to audit.
    FOR rec_link IN c_dblinks LOOP
        v_db_link_name := rec_link.NOMBREDBLINK;
        v_link_open := FALSE;

        BEGIN
            -- Building the dynamic query to obtain tablespace usage information
            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;

           -- Print headers if data found
            IF temp_details_list.COUNT > 0 THEN
                -- Check the usage percentage and whether it meets the mail sending condition
                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 headers
                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>Detalles de DBLINK: ' || v_db_link_name || '</h2>'
                    || '<p>TEMP not in use.</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('Error processing DBLINK ' || v_db_link_name || ': ' || SQLERRM);
        END;
    END LOOP;

   IF v_actions_found THEN
        MONITORING_SCHEMA.PG_ENVIO_MAIL.entrega(
            vg_from      => '[email protected]',
            vg_to        => '[email protected]',
            vg_asunto    => 'TEMP Monitoring',
            vg_cuerpo    => v_html,
            vg_firma     => 'sender',
            vg_cc        => '[email protected]'
        );
    END IF;

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

Я поставил задание выполнять процедуру каждые 5 минут, отправляя почту, когда v_used = 85 процентов памяти заполнено любым DBLINK, но мне сказали, что она не отправляет электронное письмо, когда оно заполнено, или, по крайней мере, не отправляет перехватить все табличное пространство, когда это необходимо. Мой вопрос: правильный ли основной запрос?

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'

или в моем подходе чего-то не хватает?

🤔 А знаете ли вы, что...
Oracle Database In-Memory Option - это возможность хранить данные в оперативной памяти для быстрого доступа и анализа.