Hello,
I am struggling to find the query sessions which are holding temp space.
The tempspace (temporary tablespace) is slightly increasing, and I am not able to trace which queries causing increase in occupied temp space.
SELECT MACHINE,
hash_value,
sql_text,
COUNT (*) cnt,
SUM (mb_used) mb_used
FROM ( SELECT S.sid || ',' || S.serial# sid_serial,
S.username,
s.machine,
T.blocks * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
T.sqladdr address,
Q.hash_value,
Q.sql_text
FROM v$sort_usage T,
v$session S,
v$sqlarea Q,
dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address(+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid)
GROUP BY machine, hash_value, sql_text
ORDER BY 5 DESC, 4 DESC;
I have used above query to find queries holding tempspace, I am not sure if this is the correct way.
Thanks,
Manu