oracle查看表空间的真实使用情况

--查看表空间的真实应用情形
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(+);

--查看表空间的真实应用情形
set linesize 500 pagesize 500
col