TEMP_hogger
select sum(mb_used) from
(
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM gv$sort_usage T, gv$session S, gv$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr –and s.status=’ACTIVE’
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
and t.blocks <> 0
);
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username,S.INST_ID,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM gv$sort_usage T, gv$session S, gv$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used desc;