[Oracle] 檢視tablespace的使用率(Including temp tablespace)

以前也寫過檢視tablespace的sql,
下面寫的sql包含temp tablespace的使用率的
將permanent和temporary的使用率包含在一起的.

select * from
(select x.tablespace_name,x.contents,x.status,x.extent_management,x.all_bytes/1024/1024 "ALL_SIZE(MB)",y.free_bytes/1024/1024 "FREE_SIZE(MB)",
(x.all_bytes-y.free_bytes)/1024/1024 "USED_SIZE(MB)",round((x.all_bytes-y.free_bytes)/x.all_bytes,4)*100 USAGE
from (select ts.tablespace_name,ts.contents,ts.status,ts.extent_management,sum(bytes) all_bytes from
dba_tablespaces ts, dba_data_files dbf
where ts.tablespace_name=dbf.tablespace_name group by ts.tablespace_name,ts.contents,ts.status,ts.extent_management) x,
(select tablespace_name,sum(bytes) free_bytes from dba_free_space fs group by tablespace_name) y
where x.tablespace_name=y.tablespace_name
union all
select h.tablespace_name,'TEMPORARY' contents,f.status,'LOCAL',sum(h.bytes_free+h.bytes_used)/1024/1024 "ALL_SIZE(MB)",sum((h.bytes_free+h.bytes_used-nvl(p.bytes_used,0)))/1024/1024 "FREE_SIZE(MB)",
sum(p.bytes_used)/1024/1024 "USED_SIZE(MB)",round(sum(p.bytes_used)/sum(h.bytes_free+h.bytes_used),4)*100 USAGE
from v$temp_space_header h,v$temp_extent_pool p,dba_temp_files f
where h.file_id=p.file_id(+)  and h.file_id=f.file_id
group by h.tablespace_name,f.status)
order by 8 desc