col file_name format a60
col MB form 999999.99
SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "MB" from dba_data_files order by tablespace_name,file_name
/

REM name: freespace.sql
REM 	This script is used to list database freespace, total database
REM space, largest extent, fragments and percent freespace. 

col total heading 'Total(Mb)' format 999999.9
col used heading 'Used(Mb)' format 99999.9
col pct_free heading 'Pct|Free' format 99999.9
col largest heading 'Largest(Mb)' format 99999.9

compute sum of total on report
compute sum of free on report
compute sum of used on report

break on report

select substr(a.tablespace_name,1,13) tablespace,
     round(sum(a.total1)/1024/1024, 1) Total,
     round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
     round(sum(a.sum1)/1024/1024, 1) free,
     round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1)      pct_free,
     round(sum(a.maxb)/1024/1024, 1) largest,max(a.cnt) fragment 
from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB,
      count(bytes) cnt
      from dba_free_space
      group by tablespace_name 
      union
      select tablespace_name, sum(bytes) total1, 0, 0, 0 
      from dba_data_files
      group by tablespace_name) a
group by a.tablespace_name
/