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

原文链接

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

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;
复制

2、查看指定表空间大小及其使用情况，如查看DMHR开头的表空间大小及其使用情况

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 AND T.TABLESPACE_NAME LIKE 'DMHR%';
复制

3、查看指定用户占用的空间大小，如查看SYSDBA用户占用的空间大小

SELECT USER_USED_SPACE('SYSDBA') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"

  FROM V$DM_INI

 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
复制

4、查看指定表占用的空间大小，如查看DMHR用户下CITY表占用的空间大小

SELECT TABLE_USED_PAGES('DMHR', 'CITY') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"

  FROM V$DM_INI

 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
复制

5、查看指定表已使用的空间大小，如查看DMHR用户下CITY表已使用的空间大小

SELECT TABLE_USED_SPACE('DMHR', 'CITY') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"

  FROM V$DM_INI

 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
复制

6、查看指定索引占用的空间大小，如查看DMHR用户下EMPLOYEE表已上的索引INDEX33555541占用的空间大小，达梦数据库查询索引空间占用是以索引的ID为传入参数，可以通过以下SQL查询索引的ID

SELECT NAME, ID

  FROM SYSOBJECTS

 WHERE NAME IN (SELECT INDEX_NAME

                  FROM DBA_INDEXES

                 WHERE OWNER = 'DMHR'

                   AND TABLE_NAME = 'EMPLOYEE')

   AND INDEX_NAME = 'INDEX33555541';
复制

然后根据上面查到的ID来查询对应的索引占用的空间大小

SELECT INDEX_USED_PAGES(33555524) * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"

  FROM V$DM_INI

 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
复制

7、查看指定索引已使用的空间大小，如查看DMHR用户下EMPLOYEE表已上的索引INDEX33555541使用的空间大小，达梦数据库查询索引空间占用是以索引的ID为传入参数，可以通过以下SQL查询索引的ID

SELECT NAME, ID

  FROM SYSOBJECTS

 WHERE NAME IN (SELECT INDEX_NAME

                  FROM DBA_INDEXES

                 WHERE OWNER = 'DMHR'

                   AND TABLE_NAME = 'EMPLOYEE')

   AND INDEX_NAME = 'INDEX33555541';
复制

然后根据上面查到的ID来查询对应的索引使用的空间大小

SELECT INDEX_USED_SPACE(33555524) * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"

  FROM V$DM_INI

 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
复制