临时表空间和undo表空间

查看临时表空间的具体使用情况
简易版

select s.username, s.sid, u.SQL_ID, u.tablespace, u.contents, u.segtype,     
round(u.blocks*8192/1024/1024,2) MB                                
from v$session s, v$sort_usage u                                   
where s.saddr = u.session_addr                                     
and u.contents = 'TEMPORARY'                                       
order by MB DESC ;

详细

select k.inst_id "INST_ID",  
       ktssoses "SADDR",  
       sid,  
       ktssosno "SERIAL#",  
       username "USERNAME",  
       osuser "OSUSER",   
       ktssosqlid "SQL_ID",  
       ktssotsn "TABLESPACE",  
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",  
       --注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。  
       decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',