欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-05 21:29:22
--查看表空间的真实使用情况 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_... ......
--查看表空间的真实使用情况
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