Oracle Temp Usage by Session
Displays temporary space usage for sessions currently consuming TEMP space, including username, SID, serial number, and allocated temp space in MB. Useful for monitoring heavy TEMP users and troubleshooting temp space issues.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads2 views0 comments
By OracleDba • Created
Code
(22 lines)12345678910111213141516171819202122
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/temp_usage.sql
-- Author : Tim Hall
-- Description : Displays temp usage for all session currently using temp space.
-- Requirements : Access to the V$ views.
-- Call Syntax : @temp_usage
-- Last Modified: 12/02/2004
-- -----------------------------------------------------------------------------------
COLUMN temp_used FORMAT 9999999999
SELECT NVL(s.username, '(background)') AS username,
s.sid,
s.serial#,
ROUND(ss.value/1024/1024, 2) AS temp_used_mb
FROM v$session s
JOIN v$sesstat ss ON s.sid = ss.sid
JOIN v$statname sn ON ss.statistic# = sn.statistic#
WHERE sn.name = 'temp space allocated (bytes)'
AND ss.value > 0
ORDER BY 1;