Thiết kế website giá rẻ

Question

I’ve developed this TEMP tablespaces Monitor via DBLINKS in ORACLE:

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;

    -- Declaramos una variable para almacenar detalles del temp.
    temp_details_list temp_details_table := temp_details_table();

    -- Declaramos un cursor para recorrer DBLINKS
    CURSOR c_dblinks IS
        SELECT NOMBREDBLINK || '.PGR.GOB.MX' 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;

           -- Imprimir encabezados si hay datos
            IF temp_details_list.COUNT > 0 THEN
                -- Verificar el porcentaje de uso y si cumple con la condición de envío
                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
        TICDSCC_MONITOREO.PG_ENVIO_MAIL.entrega(
            vg_from      => '[email protected]',
            vg_to        => '[email protected]',
            vg_asunto    => 'Monitoreo de TEMP',
            vg_cuerpo    => v_html,
            vg_firma     => 'sccmonitoreo',
            vg_cc        => '[email protected]'
        );
    END IF;

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

I’ve set a job to execute the procedure every 5 minutes, but I’ve been told that it does not send the email when its full or at least it does not catch the full tablespace when needed. My question is, is the main query correct?

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'

or my approach is missing something?

Thiết kế website giá rẻ

Danh mục

Is my TEMP tablespaces monitoring correct?