SQL>select s.sid || ',' || s.serial# sid_serial, s.username,
o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,
o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name ;
In RAC
select s.sid || ',' || s.serial# sid_serial, s.username,
o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,
o.sqladdr address, h.hash_value, h.sql_text
from gv$sort_usage o, gv$session s, gv$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name;
Not related
select * from dba_users where temporary_tablespace !='TEMP';
select 'alter user '||USERNAME||' temporary tablespace temp2;' from dba_users where temporary_tablespace='TEMP';
