--查看表空间的真实使用情况
set linesize 500 pagesize 500
col tablespace_name format a25
col TP_REAL_GB format a15
col TP_REAL_FREE_GB format a20
select all_tp.TP_NAME "TABLESPACE_NAME",
       to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
               'FM9999990.0099') "TP_REAL_GB",
       to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
               'FM9999990.0099') "TP_REAL_FREE_GB",
       (to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
                'FM9999990.0099') -
       to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
                'FM9999990.0099')) "TP_REAL_USED_GB",
       to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB,
                     2),
               'FM9999990.0099') || '%' "TP_FREE_RATING"
  from ( --表空间总大小
        select sum(TP_SIZE_KB) TP_ALL_SIZE_KB, TP_NAME
          from ( --自动扩展总大小(maxbytes/bytes取两者最大值)
                 select decode(sign(ddf.maxbytes - ddf.bytes),
                                1,
                                ddf.maxbytes,
                                ddf.bytes) / 1024 TP_SIZE_KB,
                         ddf.tablespace_name TP_NAME
                   from DBA_DATA_FILES ddf
                  where ddf.autoextensible = 'YES'
                 union all
                 --非自动总扩展大小
                 select BYTES / 1024 TP_SIZE_KB, ddf.tablespace_name TP_NAME
                   from DBA_DATA_FILES ddf
                  where ddf.autoextensible = 'NO') TP_ALL_SIZE
         group by TP_NAME) all_tp,
       ( --表空间空闲的总大小
        select sum(TPF_SIZE_KB) TP_FREE_SIZE_KB, TP_NAME
          from ( --数据文件已经分配，空闲空间
                 select dfs.bytes / 1024 TPF_SIZE_KB,
                         dfs.tablespace_name TP_NAME
                   from DBA_FREE_SPACE dfs
                 union all
                 --数据文件自动扩展(若maxbytes大于bytes，取差值；若maxbytes小于等于bytes，取0)
                 select decode(sign(ddf.maxbytes - ddf.bytes),
                               1,
                               ddf.maxbytes - ddf.bytes,
                               0) / 1024 TPF_SIZE_KB,
                        ddf.tablespace_name TP_NAME
                   from DBA_DATA_FILES ddf
                  where ddf.autoextensible = 'YES') TP_FREE_SIZE
         group by TP_NAME) free_tp
 where all_tp.TP_NAME = free_tp.TP_NAME(+);

TABLESPACE_NAME           TP_REAL_GB      TP_REAL_FREE_GB      TP_REAL_USED_GB TP_FREE_RATING
------------------------- --------------- -------------------- --------------- --------------
SYSAUX                    31.99           31.51                            .48 98.48%
UNDOTBS1                  31.99           31.98                            .01 99.94%
USERS                     31.99           31.99                              0 99.98%
SYSTEM                    31.99           31.33                            .66 97.93%
EXAMPLE                   31.99           31.92                            .07 99.75%
 
Elapsed: 00:00:00.18