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

-- 考虑数据文件自动扩展的情况
SELECT M.TABLESPACE_NAME,
       T.TOTAL_SPACE "TOTAL (MB)",
       M.ALLOCATE_USED_SPACE AS "USED (MB)",
       T.DATAFILE_SIZE  "FILE_SIZE (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 (SELECT TABLESPACE_NAME,BYTES  FROM DBA_FREE_SPACE
                         UNION ALL
                         -- 将UNDO已过期的段视为FREE
                        SELECT TABLESPACE_NAME,BYTES FROM DBA_UNDO_EXTENTS WHERE STATUS ='EXPIRED'
                        )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,
               ROUND(SUM(user_bytes) /1048576 ,2 ) DATAFILE_SIZE
          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);