Temp: Temp Space usage by session

/**************************************************************/
-- To see how much actual tempspace is used by each user session(if a user has multiple sessions, 
-- it will get total for each session)
/**************************************************************/


SELECT
G.USERNAME, 
g.sid,
g.serial#, 
F.TABLESPACE_NAME, 
G.TOTAL_TEMP_USED_MB, 
F.TOTAL_TEMP_MB, 
G.TOTAL_TEMP_USED_MB/F.TOTAL_TEMP_MB*100 PCT_TEMP_USED,
100 - (G.TOTAL_TEMP_USED_MB/F.TOTAL_TEMP_MB*100) PCT_TEMP_FREE
FROM 
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_TEMP_MB FROM   DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) F ,
(SELECT 
C.USERNAME ,
c.sid,
c.serial#, 
A.TABLESPACE TABLESPACE_NAME,
sum(A.BLOCKS * E.BLOCK_SIZE/1024/1024) TOTAL_TEMP_USED_MB
FROM
V$SORT_USAGE A,
V$SESSION C,
DBA_TABLESPACES E
WHERE C.SADDR=A.SESSION_ADDR
AND A.TABLESPACE = E.TABLESPACE_NAME
group by c.username, c.sid, c.serial#, tablespace
) G
WHERE F.TABLESPACE_NAME = G.TABLESPACE_NAME
/