--查Session在Temporary的實際使用量
select vs.sid,vs.serial#,vp.spid,vs.module,vs.action,vtu.username,sum(vtu.blocks)*8/1024
from V$TEMPSEG_USAGE vtu,v$session vs,v$process vp
where vtu.session_num=vs.serial#
and vtu.session_addr=vs.saddr
and vs.paddr=vp.addr
and vs.sid >10
group by vs.sid,vs.serial#,vp.spid,vs.module,vs.action,vtu.username
order by sum(blocks)*8/1024 desc;
--查temporary使用率
select a.tablespace,"實際用量(MB)","總空間(MB)","使用量(MB)","空閒量(MB)","使用率(%)",("實際用量(MB)"/"總空間(MB)"*100) "實際使用率"
from (SELECT TABLESPACE,SUM(BLOCKS)*8/1024 "實際用量(MB)" FROM V$TEMPSEG_USAGE group by tablespace) a,
(SELECT tablespace_name,
(SUM (bytes_used)+SUM (bytes_free))/1024/1024 "總空間(MB)" ,
SUM (bytes_used)/1024/1024 "使用量(MB)",
SUM (bytes_free)/1024/1024 "空閒量(MB)",
SUM (bytes_used)/(SUM (bytes_used)+SUM (bytes_free))*100 "使用率(%)"
FROM v$temp_space_header
GROUP BY tablespace_name) b
where a.tablespace=b.tablespace_name ;