0

I've developed this TEMP tablespaces Monitor via DBLINKS in ORACLE it sends an email if any temp tablespace reaches any percentage called by 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;

I've set a job to execute the procedure every 5 minutes sending the mail when v_used = 85 percent of storage full of any DBLINK, 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?

Improve this question
2
  • 1
    What debugging have you done? What do you see when you run the query, or the procedure, manually? Do you get an error from the scheduled job? What about if you remove the when others catch (which is usually a bug in itself)? Does the job run with privileges to see the link look-up table and use the links and run the email-sending procedure? Etc. Commented Aug 10, 2024 at 9:26
  • 2
    In addition to Alex's points, what problem are you trying to solve with this job? The TEMP tablespace is full of transient data. Unless you have a system that is constantly exhausting your TEMP tablespace, running the query every 5 minutes looking for 85% utilization is unlikely to catch the condition. A bad query can fill up your TEMP tablespace pretty darn quickly, generate an error, and then free up the space. The odds are low that you would catch the issue, send an email, have a human receive the email and do something to prevent tablespace exhaustion before the last 15% is used. Commented Aug 10, 2024 at 11:47

1 Answer 1

Reset to default
1

If you have a RAC database (multiple hosts mounting the DB), you would need to use gv$sort_segment (or, alternatively, gv$sort_usage) instead of the single-node v$ version, which would not capture what's being consumed by sessions in other instances to which you are not presently connected. That would undercount the amount used.

Secondly, once you correct this, you will also need to change this line to use MAX rather than SUM:

          SUM(D.mb_total) AS total_mb

Since this is at the level of the sort segment, and each instance can have a segment in each temp space, your outer query is at a lower granularity than the inner one, so SUM would multiply the already correct tablespace size to much larger than it should be. Change that SUM to MAX to fix that.

One potential issue is that gv$sort_usage will only show SQL workareas in progress - if there are none, you get nothing. That would eliminate any result from your query since you are joining on it at the end. Keep in mind that SQL sort/hash areas are not the only things that can use temp. Global temporary tables, for example, do as well but they don't show up in that view.

You can also use gv$temp_extent_pool, which accounts for all kinds of temp extents.

To be effective, you'd really need to execute this every single minute. 5 minutes is too far apart - a lot of temp can be consumed in 5 minutes. It would also be best if it ran locally rather than through a db link.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.