Tablespace Consumption

* TABLESPACES CONSUMPTION * 

set pages 10000
set linesize 125
col free format 999999.99 Heading "Free(MB)"
col total format 999999.99 Heading "Total(MB)"
col used format 999999.99 Heading "Used(MB)"
col ts_pct1 form 999 Heading "Used(%)"
col ts_pct form 999 Heading "Free(%)"
col largest format 999999.99
break on report
compute sum of free on report
compute sum of total on report
compute sum of used on report
select instance_name,substr(a.tablespace_name,1,20) 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.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/(round(sum(a.total1)/1024/1024,1))*100 ts_pct1,(round(sum(a.sum1)/1024/1024,1))/(round(sum(a.total1)/1024/1024,1))*100 ts_pct,
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, v$instance
group by instance_name,a.tablespace_name;