set lines 250
set pages 300
set numwidth 11
column "Name" format a20
column "File" format a37
column "Mount Point" format a50
column "Tablespace" format a10
column username format a10
column osuser format a10
column "SQL" format a64
column sid format 999
column serial# format 999999
column cmd format 999
column taddr format a8
column lockwait format a8
column machine format a20
column program format a30
break on "Tspc:File:Blocks:Sid:Ser:User"
column "Tspc:File:Blocks:Sid:Ser:User" format a55

select df.tablespace_name "Name",round(df.sum_bytes/1024/1024,1) "Total MB",
round(df.sum_maxbytes/1024/1024,1) "Max MB",
round(fs.sum_bytes/1024/1024,1) "Free MB",round(fs.max_bytes/1024/1024,1) 
"Max Chunk MB",
fsc.total_extents "Free Extents",fsc.extents_coalesced 
"Coal.Extents",percent_extents_coalesced "% Coal.Extents",
round(fs.sum_bytes/decode(df.sum_maxbytes,0,df.sum_bytes,df.sum_maxbytes)*100,1) 
"% Free Space",
round((0.2*df.sum_bytes-fs.sum_bytes)/0.8/1024/1024,1) "MB to add"
from
(select tablespace_name,sum(bytes) sum_bytes,sum(maxbytes) sum_maxbytes 
from dba_data_files group by tablespace_name) df
left outer join
(select tablespace_name,sum(bytes) sum_bytes,max(bytes) max_bytes from 
dba_free_space group by tablespace_name) fs 
on (df.tablespace_name=fs.tablespace_name)
left outer join dba_free_space_coalesced fsc
on (df.tablespace_name=fsc.tablespace_name)
order by "% Free Space";