Tablespace Space Check

set pages 1000;
set lines 120;
set feedback off;
col totsiz  format 999,999,990 justify c heading 'Total|(MB)'
col avasiz  format 999,999,990 justify c heading 'Free|(MB)'
col pctusd  format         990 justify c heading 'Pct|Used'
col maxsiz  format 999,999,990 justify c heading 'MaxExtend|(MB)'
col "Total Free" format 999,999,990 justify c heading 'MaxFree|(MB)'
col "Actual pctfree" format         990 justify c heading 'MaxPct|Free'
comp sum of totsiz avasiz "Total Free" on report
break on report

select
 total.tablespace_name,
 round(total.bytes/1024/1024)                            totsiz,
 round(nvl(sum(free.bytes)/1024/1024,0))                 avasiz,
 round((1-nvl(sum(free.bytes),0)/total.bytes)*100)  pctusd ,
 round(decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024)                           maxsiz,
 round(nvl(sum(free.bytes)/1024/1024,0) + (decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024 - total.bytes/1024/1024)) "Total free",
 round((nvl(sum(free.bytes)/1024/1024,0) + (decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024 - total.bytes/1024/1024))*100/(decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024)) "Actual pctfree"
from
 (select sum(bytes) bytes,tablespace_name,sum(decode(maxbytes,0,bytes,maxbytes)) maxbytes from dba_data_files  group by tablespace_name)  total,
 (select sum(bytes) bytes,tablespace_name from dba_free_space  group by tablespace_name ) free where
 total.tablespace_name = free.tablespace_name(+)
group by
 total.tablespace_name,
 total.bytes,
 decode(total.maxbytes,0,total.bytes,total.maxbytes)
/