REM Tablespace usage
set lin 120 pages 500
col tablespace_name format a15 trunc
col cur_use_mb for 999999999
col cur_sz_mb for 999999999
col free_space_mb for 999999999
col max_sz_mb for 999999999
compute sum of cur_use_mb on report
compute sum of cur_sz_mb on report
compute sum of free_space_mb on report
compute sum of max_sz_mb on report
break on report
select tablespace_name,
round(sum(total_mb)-sum(free_mb),2) cur_use_mb,
round(sum(total_mb),2) cur_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(total_mb)*100) cur_prct_full,
round(sum(max_mb) – (sum(total_mb)-sum(free_mb)),2) free_space_mb,
round(sum(max_mb),2) max_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(max_mb)*100) overall_prct_full
from (select tablespace_name,sum(bytes)/1024/1024 free_mb,0 total_mb,0 max_mb from DBA_FREE_SPACE group by tablespace_name
union select tablespace_name,0 current_mb,sum(bytes)/1024/1024 total_mb,sum(decode(maxbytes, 0, bytes, maxbytes))/1024/1024 max_mb
from DBA_DATA_FILES group by tablespace_name) a group by tablespace_name;