达梦数据库表空间大小查看

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 、查看指定表空间大小及其使用情况，如查看DMHS开头的表空间大小及其使用情况

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 'DMHS%';

 

 

 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 、查看指定表占用的空间大小，如查看DEM用户下ADMIN_AUDIT表占用的空间大

SELECT TABLE_USED_PAGES('DEM', 'ADMIN_AUDIT') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';

 

 

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

SELECT TABLE_USED_SPACE('DEM', 'ADMIN_AUDIT') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';

 

 

 6 、查看指定索引占用的空间大小，如查看DEM用户下ADMIN_AUDIT表上的索引INDEX33555477占用的空间大小，达梦数据库查询索引空间占用是以索引的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(33555477) * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';