테이블스페이스 사용량 체크 쿼리

select tf.tablespace_name ts_name,sum(tf.total_space) total_size
,round(sum(tf.total_space-ts.free_space),1) used_size
,sum(round(ts.free_space,1)) free_size
,round(sum(tf.total_space-ts.free_space)/sum(tf.total_space),3)*100 used_ratio
from
(select tablespace_name, sum(bytes)/1024/1024 free_space from dba_free_space group by tablespace_name) ts,
(select tablespace_name,sum(bytes)/1024/1024 total_space from dba_data_files
group by tablespace_name) tf
where
tf.tablespace_name=ts.tablespace_name
group by tf.tablespace_name
order by 5 desc;



===============================================

결과



TS_NAME TOTAL_SIZE USED_SIZE FREE_SIZE USED_RATIO
------------------------------ ---------- ---------- ---------- ----------
SYSTEM 420 419.4 .6 99.9
EXAMPLE 138.75 138.3 .5 99.6
XDB 46.875 44.3 2.6 94.4
CWMLITE 20 17.6 2.4 87.8
DRSYS 20 9.7 10.3 48.4
ODM 20 9.4 10.6 46.9
TS_CDRDATA 10000 952 9048 9.5
UNDOTBS1 430 21.4 408.6 5
TOOLS 10 .1 9.9 .6
INDX 25 .1 24.9 .3
USERS 25 .1 24.9 .3