free_space2.sql

clear buffer
clear columns
clear breaks
column a1 heading 'Tablespace' format a15
column a2 heading 'data File' format a45
column a3 heading 'Total|Space' format 99999.99
column a4 heading 'Free|Space' format 99999.99
column a5 heading 'Free|perc' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
select a.tablespace_name a1, a.file_name a2, a.avail a3, nvl(b.free,0) a4,
       nvl(round(((free/avail)*100),2),0) a5
from (select tablespace_name, substr(file_name,1,45) file_name,
             file_id, round(sum(bytes/(1024*1024)),3) avail
      from   sys.dba_data_files
      group by  tablespace_name, substr(file_name,1,45),
                file_id) a,
      (select tablespace_name, file_id,
             round(sum(bytes/(1024*1024)),3) free
      from   sys.dba_free_space
      group by tablespace_name, file_id) b
where a.file_id = b.file_id (+)
order by 1, 2
/