表空间利用率 | 达梦技术社区

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:8
【操作系统】：7
【CPU】:2
【问题描述】*：我更改了表空间的maxsize ，那我利用如下看利用率，计算是按maxsize计算的吗还是按目前文件大小SELECT F.TABLESPACE_NAME,
F.FREE_SPACE AS "FREE_SIZE",
T.TOTAL_SPACE "TOTAL_SIZE"
FROM ( SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024
FROM V$DM_INI
WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

回答 0

暂无回答

S-surprise-H

文件大小去做的百分比利用率，参考一下如下
select c.name 表空间名字,
d.total 总大小M,
d.use 当前使用大小M,
d.free 剩余大小M,
round(d.used_per, 2) || '%' 使用百分比,
e.max_size 上限大小M
from V$TABLESPACE c
join ( SELECT a.id,
sum(b.total_size) * b.page_size / 1024 / 1024 total,
sum(b.free_size) * b.page_size / 1024 / 1024 free,
sum(b.total_size * b.page_size / 1024 / 1024 - b.free_size * b.page_size / 1024 / 1024) use,
100 - (sum(b.free_size) * 100 / sum(b.total_size)) used_per
FROM V$TABLESPACE a,
V$DATAFILE b
where a.id = b.GROUP_ID
group by a.id,
b.page_size ) d
on c.id = d.id
left join v$datafile e
on c.id = e.group_id
order by round(d.used_per, 2) desc;

发布于 2025/09/01 15:48

聊乐

如果开启了自增且设置了最大值，建议在查询表空间使用率的时候将允许自增空间计算在内。可参考如下SQL:

SELECT *
  FROM (SELECT T.NAME,
                G.TOTAL_MB,
                G.USED_MB,
                G.FREE_MB,
                G.USED_PERCENT,
                CASE WHEN G.USED_PERCENT <= 85 OR G.FREE_MB / 1024 > 60 THEN 'NORMAL' WHEN G.USED_PERCENT > 85 AND G.USED_PERCENT <= 90 THEN 'WARNING' WHEN G.USED_PERCENT > 90 THEN 'ERROR' END INSPECTION_RESULT
           FROM ( SELECT GDF.GROUP_ID,
                         SUM(GDF.FILE_SIZE_MB) TOTAL_MB,
                         SUM(FILE_USED_MB)USED_MB,
                         SUM(GDF.FILE_SIZE_MB)- SUM(GDF.FILE_USED_MB) FREE_MB,
                         TO_CHAR(ROUND(SUM(FILE_USED_MB)/SUM(GDF.FILE_SIZE_MB)*100, 2), '990.99') USED_PERCENT
                    FROM ( SELECT DF.GROUP_ID,
                                 round(CASE WHEN DF.AUTO_EXTEND = 1 THEN DF.MAX_SIZE ELSE DF.TOTAL_SIZE*DF.PAGE_SIZE/1024/1024 END,1) FILE_SIZE_MB,
                                 round((DF.TOTAL_SIZE - DF.FREE_SIZE)*DF.PAGE_SIZE/1024/1024,1) FILE_USED_MB
                            FROM V$DATAFILE DF) GDF
                GROUP BY GDF.GROUP_ID) G,
                V$TABLESPACE T
          WHERE G.GROUP_ID = T.ID
       ORDER BY 5 DESC)
 WHERE ROWNUM<=40;

发布于 2025/09/01 21:39