Oracle表空间容量计算

tongy

-- 数据表空间容量使用情况
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

son30

美女未抱身先走,常使色狼泪满襟。。。。。。

桀昊j

有竞争才有进步嘛

inushome

如果回帖是一种美德，那我早就成为圣人了!

奥尔覅几22

学习了，不错，讲的太有道理了

q456123

为中华而努力读书!一包中华好多钱啊~~~

华风

真是 收益 匪浅