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 => 'sender@mail.com',
vg_to => 'receipt@mail.com',
vg_asunto => 'TEMP Monitoring',
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;
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?
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.