Freespace by Tablespace

set pagesize 86
ttitle skip 2 center 'FREE - Free space by Tablespace' skip 2
column dummy noprint
column pct_used format 999.9 heading "Percentage_Used"
column name format a16 heading "Tablespace Name"
column bytes format 999,999,999,999,999 heading "Total MBytes"
column used format 999,999,999,999,999 heading "Used MB"
column free format 999,999,999,999,999 heading "Free MB"
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report
spool freespace.lst
set termout on



select a.tablespace_name name,
sum(b.bytes/1024/1024)/count( distinct a.file_id||'.'||a.block_id ) Megabytes,
sum(b.bytes/1024/1024)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes/1024/1024)/count( distinct b.file_id ) usedSpace,
sum(a.bytes/1024/1024)/count( distinct b.file_id ) freespace,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used;



spool off
set termout on