本文提供了一段SQL查询代码，用于分析Oracle数据库中各表空间的总容量、已使用容量、使用率、剩余容量及空闲率。通过对这些数据的统计，数据库管理员可以更好地了解表空间的使用情况。
select a.tablespace_name,
b.total/(1024*1024) "total M",
(b.total - nvl(c.free, 0))/(1024*1024) "used M",
round(((b.total - nvl(c.free, 0))/b.total)*100, 2) "% used",
nvl(c.free, 0)/(1024*1024) "free M",
round((nvl(c.free, 0)/b.total)*100, 2) "% free"
from dba_tablespaces a,
(select tablespace_name, sum(bytes) total
from dba_data_files group by tablespace_name) b,
(select tablespace_name, sum(bytes) free
from dba_free_space group by tablespace_name) c
where a.tablespace_name = b.tablespace_name and
a.tablespace_name = c.tablespace_name (+);
b.total/(1024*1024) "total M",
(b.total - nvl(c.free, 0))/(1024*1024) "used M",
round(((b.total - nvl(c.free, 0))/b.total)*100, 2) "% used",
nvl(c.free, 0)/(1024*1024) "free M",
round((nvl(c.free, 0)/b.total)*100, 2) "% free"
from dba_tablespaces a,
(select tablespace_name, sum(bytes) total
from dba_data_files group by tablespace_name) b,
(select tablespace_name, sum(bytes) free
from dba_free_space group by tablespace_name) c
where a.tablespace_name = b.tablespace_name and
a.tablespace_name = c.tablespace_name (+);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
