TABLESPACE USAGE SCRIPTS of a ORACLE database

TABLE SPACE USAGE SCRIPTS of a ORACLE database


set pages 60

column  pct_used format 999.9       heading "%|Used" 
column  ts_name    format a21      heading "Tablespace Name" 
column  Mbytes   format 999,999,999    heading "MBytes" 
column  used    format 999,999,999   heading "Used" 
column  free    format 999,999,999  heading "Free" 
column  largest    format 999,999,999  heading "Largest" 
break   on report 
compute sum of Mbytes on report 
compute sum of free on report 
compute sum of used on report 



select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) ts_name
, Mbytes_alloc Mbytes
, Mbytes_alloc-nvl(Mbytes_free,0) used
, nvl(Mbytes_free,0) free
, ((Mbytes_alloc-nvl(Mbytes_free,0))/Mbytes_alloc)*100 pct_used
, nvl(largest,0) largest
from (select sum(bytes)/1024/1024 Mbytes_free
, max(bytes)/1024/1024 largest
, tablespace_name
from dba_free_space
group by tablespace_name) a
, (select sum(bytes)/1024/1024 Mbytes_alloc
, tablespace_name
from dba_data_files
group by tablespace_name) b
where a.tablespace_name (+) = b.tablespace_name
order by 5,1;