--查看表空间的真实使用情况
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
