-> Check Total TEMP usage:
SQL>
SELECT B.TOTAL_MB,
B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
FROM V$SORT_SEGMENT A,
(SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;
-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
S.USERNAME,
U.TABLESPACE,
S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
U.SEGTYPE,
U.CONTENTS,
U.BLOCKS
FROM V$SESSION S, V$TEMPSEG_USAGE U
WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;
-> Find problematic SQL for TEMP
SQL>
SELECT
S.SID
,S.CLIENT_INFO
,S.MACHINE
,S.PROGRAM
,S.TYPE
,S.LOGON_TIME
,S.OSUSER
,SQ.SORTS
,SQ.DISK_READS
,SQ.BUFFER_GETS
,SQ.ROWS_PROCESSED
,SQ.SQLTYPE
,SQ.SQL_TEXT
FROM GV$SESSION S
, GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
--AND s.inst_id= 1
AND S.SID = 555
AND SQ.INST_ID= S.INST_ID;