--查看表空間的真實使用情況 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
