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(+);

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