Query to find the informations about temporary tablespace usages

Find the session which are using temporary tablespace

SELECT S.SID SID,S.serial# sid_serial, S.username username , S.osuser OSuser , P.spid processid, S.module Module,

S.program program , SUM (SU.blocks) * T.block_size / 1024 / 1024/1024  used_space_GB, SU.TABLESPACE,

COUNT(*) sort_oprestions

FROM v$sort_usage SU, v$session S, DBA_TABLESPACES T, v$process P

WHERE SU.session_addr = S.saddr

AND S.paddr = P.addr

AND SU.TABLESPACE = T.tablespace_name

GROUP BY S.SID, S.serial#, S.username, S.osuser, P.spid, S.module,

S.program, T.block_size, SU.TABLESPACE

ORDER BY sid_serial;

Find Total temp space used by all the session

SELECT SS.tablespace_name TABLESPACE, D.total_GB,

SUM (SS.used_blocks * D.block_size) / 1024 / 1024/1024 used_GB,

D.total_GB - SUM (SS.used_blocks * D.block_size) / 1024 / 1024/1024 free_GB

FROM v$sort_segment SS,

(

SELECT T.NAME, TM.block_size, SUM (TM.bytes) / 1024 / 1024/1024 total_GB

FROM v$tablespace T, v$tempfile TM

WHERE T.ts#= TM.ts#

GROUP BY T.NAME, TM.block_size

) D

WHERE SS.tablespace_name = D.NAME

GROUP BY SS.tablespace_name, D.total_GB;

Find the  each statement using the temp tablespace

SELECT S.SID SID , S.serial# serial, S.username USERNAME,

T.blocks * TBS.block_size / 1024 / 1024/1024 used_GB, T.TABLESPACE,

T.sqladdr address, SA.hash_value, SA.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea SA, DBA_TABLESPACES TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = SA.address (+)

AND T.TABLESPACE = TBS.tablespace_name

ORDER BY S.SID;