Find Who And What SQL Is Using Temp Segments

 How Do You Find Who And What SQL Is Using Temp Segments

SELECT b.tablespace,

       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
        a.inst_id as Instance,
        a.sid||','||a.serial# AS sid_serial,
        NVL(a.username, '(oracle)') AS username,
        a.program,
        a.status,
        a.sql_id,
c.sql_text
 FROM   gv$session a,
        gv$sort_usage b,
        gv$parameter p,
gv$sqlarea c
 WHERE  p.name  = 'db_block_size'
 AND    a.saddr = b.session_addr
 AND    a.inst_id=b.inst_id
 AND    c.address= a.sql_address
 AND    c.hash_value = a.sql_hash_value
 AND    a.inst_id=p.inst_id
 AND    a.sql_id is not null
 ORDER BY b.tablespace, b.blocks
 /

From Oracle:-

SELECT x.username, x.sid, x.serial#, x.osuser, y.tablespace, y.blocks, z.sql_text

FROM v$session x, v$tempseg_usage y, v$sqlarea z

WHERE x.saddr = y.session_addr

AND z.address= x.sql_address

AND z.hash_value = x.sql_hash_value

ORDER BY y.tablespace, y.Blocks;

Oracle Mean company, not database :)