Oracle表空间容量的计算

-- 数据表空间容量使用情况

SELECT a.tablespace_name as "表空间名称",

to_char(b.total/1024/1024,999999.99)||'M'  as "总容量",

to_char((b.total-a.free)/1024/1024,'9999990D99')||'M'  as "已使用容量",

to_char(a.free/1024/1024,'9999990D99')||'M' as "剩余容量",

to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as "已使用百分比"

FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,

(SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b

WHERE a.tablespace_name=b.tablespace_name

ORDER BY round((total-free)/total,4) DESC;

--查看临时表空间容量使用情况

select d.tablespace_name "表空间名称",space "总容量(M)",

used_space "已使用容量(M)",round(nvl(used_space,0)/space*100,2) "已使用百分比(%)",

nvl(free_space,0) "剩余容量(M)"

from

(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocks

from dba_temp_files

group by tablespace_name) d,

(select tablespace_name,round(sum(bytes_used)/(1024*1024),2) used_space,

round(sum(bytes_free)/(1024*1024),2) free_space

from v$temp_space_header

group by tablespace_name) f

where  d.tablespace_name = f.tablespace_name(+)

--UNDO表空间

SELECT

seg.tablespace_name "Tablespace Name",

ts.bytes/1024/1024 "TS Size(MB)",

ue.status "UNDO Status",

count(*)  "Used Extents",

round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",

round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"

FROM dba_segments seg, DBA_UNDO_EXTENTS ue,

(SELECT tablespace_name, sum(bytes) bytes

FROM dba_data_files GROUP BY tablespace_name) ts

WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name

GROUP BY seg.tablespace_name, ts.bytes, ue.status ORDER BY seg.tablespace_name;

--表空间需要的存储估算

计算公式：评估存储合计=在网用户数*平均每用户每天话单数*每话单长度（K）*数据库内保留天数/1024/1024/0.75

本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/