Oracle表空间使用情况统计

网上搜到的那些SQL基本上都是没有考虑数据文件自动扩展的情况，查出来的结果不准确，会干扰监控告警，于是撸了一个完善点的，兼顾了数据文件自增长和非自增长的情况，供大家参考。

### 考虑数据文件自动扩展的情况
 SELECT M.TABLESPACE_NAME,
       T.TOTAL_SPACE "TOTAL (MB)",
       M.ALLOCATE_USED_SPACE AS "USED (MB)",
       T.TOTAL_SPACE - M.ALLOCATE_USED_SPACE "FREE (MB)",
       ROUND(((T.TOTAL_SPACE-M.ALLOCATE_USED_SPACE)/T.TOTAL_SPACE)*100)||'%' "FREE (%)"
  FROM (SELECT T.TABLESPACE_NAME,T.ALLOCATE_SPACE - F.FREE_SPACE AS ALLOCATE_USED_SPACE
          FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES )/1048576,2) FREE_SPACE
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) F,
               (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576), 2) ALLOCATE_SPACE
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) T
         WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME) M,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(CASE WHEN MAXBYTES = 0 THEN BYTES ELSE MAXBYTES END / 1048576), 2) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
 WHERE M.TABLESPACE_NAME = T.TABLESPACE_NAME
-- AND T.TABLESPACE_NAME = 'xxxxx'
 ORDER BY ROUND(((T.TOTAL_SPACE-M.ALLOCATE_USED_SPACE)/T.TOTAL_SPACE)*100,2);
 
### 不考虑数据文件自动扩展的情况
 SELECT F.TABLESPACE_NAME,
       T.TOTAL_SPACE "TOTAL (MB)",
       (T.TOTAL_SPACE - F.FREE_SPACE) AS "USED (MB)",
       F.FREE_SPACE "FREE (MB)",
       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100, 2)) || '%' "FREE (%)"
  FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES )/1048576,2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576), 2) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
  -- AND F.TABLESPACE_NAME = 'xxxxx'
 ORDER BY ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100,2);