Oracle查询表空间使用情况

Oracle查询表空间使用情况

可用根据DBA_DATA_FILES 和 DBA_FREE_SPACE两个视图来查询

• DBA_FREE_SPACE ：字段BYTES，表示该表空间 已用空间
• DBA_DATA_FILES ：字段BYTES， 表示该表空间 当前可使用的总空间
• DBA_DATA_FILES ：字段MAXBYTES， 表示该表空间 当前可扩展到的最大空间

SELECT TOTAL.TABLESPACE_NAME AS 表空间名,
       ROUND(TOTAL.MB - FREE.MB, 2) || ' MB' AS 当前已用空间,
       ROUND(FREE.MB, 2) || ' MB' AS 当前可用空间,
       ROUND(TOTAL.MB, 2) || ' MB' AS 当前可用的总空间,
       ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS 当前使用百分比,
       ROUND(TOTAL.MAX_MB, 2) || ' MB' AS 可扩展到的最大空间,
       DECODE(TOTAL.MAX_MB, 0, 0, ROUND(TOTAL.MAX_MB - TOTAL.MB, 2)) ||
       ' MB' AS 剩余可扩展的空间,
       DECODE(TOTAL.MAX_MB,
              0,
              0,
              ROUND((1 - TOTAL.MB / TOTAL.MAX_MB) * 100, 2)) || '%' AS 剩余可扩展的百分比,
       ROUND(TOTAL.MAX_MB - FREE.MB, 2) || ' MB' AS 剩余可用的最大空间
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) FREE,
       (SELECT TABLESPACE_NAME,
               SUM(BYTES) / 1024 / 1024 AS MB,
               SUM(MAXBYTES) / 1024 / 1024 AS MAX_MB
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) TOTAL
 WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
 ORDER BY TOTAL.TABLESPACE_NAME