O Script abaixo relata o consumo por sessao da tablespace TEMP.

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*8192)/1024, c.sql_textFROM v$session a, v$tempseg_usage b, v$sqlarea cWHERE b.tablespace = ‘TMPTESTE’and a.saddr = b.session_addrAND c.address= a.sql_addressAND c.hash_value = a.sql_hash_valueAND b.blocks*8192 > 1024;