关闭

表空间使用率

309人阅读 评论(0) 收藏 举报
分类:

SELECT c.tablespace_name "表空间", ROUND(a.bytes/1048576,2) "表空间大小",ROUND((a.bytes-b.bytes)/1048576,2) "已使用空间",ROUND(b.bytes/1048576,2) "剩余空间",
ROUND(b.bytes/a.bytes * 100,2)||'%' "剩余百分比" FROM
(SELECT tablespace_name,SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
ORDER BY ROUND(b.bytes/1048576,2);

 

 

 

 

select a.tablespace_name,round(nvl(sum(a.bytes)/1024/1024,0),2) "ALL_SIZE(M)",round(nvl(sum(b.bytes)/1024/1024,0),2) "USED_SIZE(M)",
round(sum(c.bytes)/1024/1024,2) "FREE_SIZE(M)",
round(100*nvl(sum(c.bytes),0)/sum(a.bytes),2)||'%' "FREE_PCT(%)"
from sm$ts_avail a,sm$ts_used b,sm$ts_free c
where a.tablespace_name=b.tablespace_name(+) and a.tablespace_name=c.tablespace_name(+)
group by a.tablespace_name
order by 3;

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:27281次
    • 积分:555
    • 等级:
    • 排名:千里之外
    • 原创:23篇
    • 转载:16篇
    • 译文:1篇
    • 评论:0条