【达梦数据库】表空间等空间大小查询方法总结

参考链接

链接: 达梦数据库表空间等空间大小查询方法总结

查看所有表空间大小及其使用情况

SELECT F.TABLESPACE_NAME,

       (T.TOTAL_SPACE - F.FREE_SPACE) / 1024 "USED (GB)",

       F.FREE_SPACE / 1024 "FREE (GB)",

       T.TOTAL_SPACE / 1024  "TOTAL(GB)",

       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE

  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;

查询表空间 PESDATA 中所有表的详细占用空间

如果只返回前50行，在达梦数据库（DM8及以上版本）中，支持类似Oracle 12c的 FETCH FIRST 语法，可直接限制结果数量：

-- 查询表空间PESDATA中占用最高的前50个表（含数据和索引）
SELECT 
    t.owner AS "所属用户",
    t.table_name AS "表名",
    ROUND(s.seg_size / 1024 / 1024, 2) AS "表数据空间(MB)",
    ROUND(idx.idx_size / 1024 / 1024, 2) AS "索引空间(MB)",
    ROUND((s.seg_size + COALESCE(idx.idx_size, 0)) / 1024 / 1024, 2) AS "总占用空间(MB)"
FROM 
    dba_tables t
LEFT JOIN (
    SELECT owner, segment_name, SUM(bytes) AS seg_size
    FROM dba_segments
    WHERE segment_type = 'TABLE' AND tablespace_name = 'PESDATA'
    GROUP BY owner, segment_name
) s ON t.owner = s.owner AND t.table_name = s.segment_name
LEFT JOIN (
    SELECT i.owner, i.table_name, SUM(s.bytes) AS idx_size
    FROM dba_indexes i
    JOIN dba_segments s ON i.index_name = s.segment_name
    WHERE s.segment_type = 'INDEX' AND s.tablespace_name = 'PESDATA'
    GROUP BY i.owner, i.table_name
) idx ON t.owner = idx.owner AND t.table_name = idx.table_name
WHERE t.tablespace_name = 'PESDATA'
ORDER BY "总占用空间(MB)" DESC
FETCH FIRST 50 ROWS ONLY;  -- 直接限制返回前50行