Thursday, November 16, 2006

Display tablespace usage

Thursday, November 16, 2006 Posted by Andre Broers
This script shows the tablespace usage.
src: http://www.oracle-consultant.co.uk

 break on report  
 compute sum of tbs_size_mb on report  
 compute sum of used    on report  
 compute sum of avail    on report  
 column tsname    format a20     heading ‘Tablespace Name’  
 column tbs_size_mb format 999,999   heading ‘Size|(MB)’  
 column used     format 999,999   heading ‘Used|(MB)’  
 column avail    format 999,999   heading ‘Free|(MB)’  
 column used_visual format a11     heading ‘Used’  
 column pct_used   format 999     heading ‘% Used’  
 set linesize 1000  
 set trimspool on  
 set pagesize 32000  
 set verify  off  
 set feedback off  
 PROMPT  
 PROMPT *************************  
 PROMPT *** TABLESPACE STATUS ***  
 PROMPT *************************  
 SELECT  df.tablespace_name              tsname  
 ,    sum(df.bytes)/1024/1024           tbs_size_mb  
 ,    nvl(sum(e.used_bytes)/1024/1024,0)      used  
 ,    nvl(sum(f.free_bytes)/1024/1024,0)      avail  
 ,    rpad(’ ‘||rpad(’X',round(sum(e.used_bytes)  
 *10/sum(df.bytes),0), ‘X’),11,’-')      used_visual  
 ,    nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used  
 FROM   sys.dba_data_files df  
 ,    (SELECT  file_id  
 ,    sum(nvl(bytes,0)) used_bytes  
 FROM   sys.dba_extents  
 GROUP BY file_id) e  
 ,    (SELECT  max(bytes) free_bytes  
 ,    file_id  
 FROM   dba_free_space  
 GROUP BY file_id) f  
 WHERE  e.file_id(+) = df.file_id  
 AND   df.file_id  = f.file_id(+)  
 GROUP BY df.tablespace_name  
 ORDER BY 6  
 /  
 clear breaks