在有些情况下表空间的数据文件既有自动扩展的也有非自动扩展的，用普通的表空间容量查询语句得到的结果一般不准确，为了解决这个问题我进行了区分统计，首先得到TOTAL_MB这个列，TOTAL_MB的值来自于数据文件的BYTES(非自动扩展)与数据文件最大值32G(自动扩展)的和；然后再用dba_segments取到每一个表空间下含有所有段的大小总和也就是USED_MB. 这连个值都取到以后，表空间的剩余量和使用率就自然得到了：参考下面

点击(此处)折叠或打开

select F0.TBNAME,
F0.TOTAL_MB,
NVL(F1.USED_MB,0) TOTAL_USED_MB,
(F0.TOTAL_MB - NVL(F1.USED_MB,0)) TOTAL_FREE_MB,
round((NVL(F1.USED_MB,0) / F0.TOTAL_MB) * 100, 2) || '%' "%USED"
from
(select t1.tbname TBNAME, sum(t1.MBYTES) TOTAL_MB
from (select tablespace_name tbname,
(case autoextensible
when 'YES' then
32768
when 'NO' then
bytes / 1024 / 1024
end) MBYTES
from dba_data_files
group by tablespace_name, autoextensible, bytes) t1
group by t1.tbname) F0,
(select tablespace_name TBNAME,
round(NVL(sum(bytes /1024/ 1024),0), 2) USED_MB
from dba_segments
group by tablespace_name) F1
where F0.TBNAME = F1.TBNAME(+);

下面这条统计表空间使用率的SQL也很强大，加上了对临时表空间查询，但没有做到自动区分数据文件是自动扩展还是非自动扩展的。不妨参考下：

点击(此处)折叠或打开

SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(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(+)
ORDER BY 1;

ITPub博客

oracle万能表空间使用率查询SQL

最新文章