网上搜到的那些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);
