select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc ;
TABLESPACE_NAME Sum MB used MB free MB percent_used
--------------- ------ ----------- ---------- ------------
CBS 420000 325152.8125 94847.1875 77.42
IND_CBS 180720 134444.1875 46275.8125 74.39
SYSAUX 30000 1895.8125 28104.1875 6.32
SYSTEM 30000 1242.6875 28757.3125 4.14
OGG_TS 30720 515.125 30204.875 1.68
UNDOTBS1 60000 609.75 59390.25 1.02
UNDOTBS2 60000 406.5 59593.5 0.68
USERS 1024 1 1023 0.1
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;