Temp utilization..How much?

Sometimes we need to monitor the Temp utilization as we use to get a request that Temp Table is going out of space even if that is pretty BIG enough 40 to 60 GB..we have to catch the utilization and monitor who is ueing and how much. So i used this for that ...and then further investigation for the Root cause of using so much of Temp and Why?

--Execute the following query to determine who is using a TEMP Segment:


set line 200

column sid_serial format a20



SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)

'M' "SIZE",

a.sid

','

a.serial# SID_SERIAL,

a.username,

a.program

FROM sys.v_$session a,

sys.v_$sort_usage b,

sys.v_$parameter p

WHERE p.name = 'db_block_size'

AND a.saddr = b.session_addr

ORDER BY b.tablespace, b.blocks;





TABLESPACE SIZE SID_SERIAL USERNAME PROGRAM

---------- ------- ---------- -------- ------------------------------

TEMP 24M 260,7 SCOTT sqlplus@localhost.localdomain

(TNS V1-V3)