temporary tablespace usage in oracle - find sessiosn performing sorting

Oracle provides two basic dynamic performance views for online monitoring of temporary tablespace usage. These are v$sort_usage to query online sessiosn using temporary segments and v$sort_segment to query the size of the temporary segements

V$SORT_USAGE has below main colums:

USERNAME – database user name
SESSION_ADDR – address of the session, can be used to identify the session in V$SESSION according to the SADDR column
SQL_ID – the identifier of the SQL that requires the sort or join, can be used to identify the SQL from V$SQL according to the SQL_ID column
EXTENTS – number of extents in the temporary segment being used by this session
BLOCKS – number of blocks in the temporary segment being used by this session

--based on v$sort_usage
--temp segment usage per session

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

This sid and serial# can be used to find current query that is taking temp segment using view v$sqltext and passing sql_id of the sid and serial#

-- based on v$sort_segment
-- listing of temp segments

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;