set pages 999
set lines 200
col tablespace for a30
select tablespace,used_mb,free_mb,total_mb,max_mb,pct_used,pct_used_max
from (
select
                total.ts tablespace,
                total.bytes - nvl(free.bytes,0) used_mb,
                free.bytes free_mb,
                total.bytes total_mb,
                total.MAXBYTES max_mb,
                100-ROUND( (nvl(free.bytes,0))/(total.bytes) * 100, 2) pct_used,
                100-ROUND( (  nvl(total.MAXBYTES,0)  - (nvl(total.bytes,0)-nvl(free.bytes,0))  )/(total.MAXBYTES) * 100, 2) pct_used_max
from
                (select tablespace_name ts, round(sum(bytes)/1024/1024,2) bytes, round(sum(decode(MAXBYTES,0,BYTES,MAXBYTES))/1024/1024,2) MAXBYTES
                               from dba_data_files group by tablespace_name) total,
                (select tablespace_name ts, round(sum(bytes)/1024/1024,2) bytes from dba_free_space group by tablespace_name) free
where total.ts=free.ts(+) )
order by 7 desc;
