基本上正确
-
-
select a.tablespace_name,round(100*(a.total_mb-b.free_mb)/a.extent_mb,2) used_pct
-
from (select tablespace_name,round(sum(nvl(bytes,0))/1024/1024,2) total_mb,
-
round(sum(greatest(maxbytes,bytes))/1024/1024,2) extent_mb
-
from dba_data_files group by tablespace_name) a,
-
(select tablespace_name,round(sum(nvl(bytes,0))/1024/1024,2) free_mb
-
from dba_free_space group by tablespace_name) b
-
where a.tablespace_name = b.tablespace_name(+)
-
union all
-
select a.tablespace_name,round(100*(b.used_mb)/a.extent_mb,2)
-
from (select tablespace_name,round(sum(greatest(maxbytes,bytes))/1024/1024,2) extent_mb
-
from dba_temp_files group by tablespace_name) a,
-
(select tablespace_name,sum(bytes_used)/1024/1024 used_mb
-
from gv$temp_extent_pool group by tablespace_name) b
-
where a.tablespace_name = b.tablespace_name(+)
-
order by 1;
-
如果文件开启了自动扩展,那么按可扩展的 greatest() 值计算,而实际使用时要注意磁盘空间是否支持
如果创建了一个bigfile表空间且数据没超过1T,那么这个使用率可能很低(<3%)。
阅读(1476) | 评论(0) | 转发(0) |