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

tolilong發表於2016-06-19
以前也寫過檢視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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2120428/,如需轉載,請註明出處,否則將追究法律責任。

相關文章