查看临时表空间的具体使用情况
简易版
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',