Unix script to gather tablespace information

This UNIX script is used to gather tablespace information: space, used_space, fragmentation, and more. To use it, place it into your $HOME/bin so you can launch it wherever you are.

#!/bin/csh -f
# this script was made to view a tablespace view, fragmentation, free space
# usage = space DB_NAME (ORACLE_SID)

setenv ORACLE_SID $1
setenv LOG tbsp_frag`date "+%d-%m-%Y"`.$ORACLE_SID.txt
sqlplus internal <<EOF
spool $LOG
column tablespace_name format a16
column files           heading "no|file" format 9999
column total_kb        heading "total|KB" format 99,999,999
column free_kb         heading "free|KB" format 99,999,999 print
column fragments       heading "no|frags" format 9999
column max_frag        heading "lrgst|frag" format 9,999,999
column min_frag        heading "smlst|frag" format 9,999,999
column pct_used        heading "PCT|Used" format 999
column lfi             heading "FSFI" format 999 noprint
column tfi             heading "TFI"  format 999 noprint
column used_kb         heading "Used|KB" format 99,999,999 noprint

break on report
compute sum of files on report
compute sum of total_kb on report
compute sum of free_kb on report
compute sum of used_kb on report

   select a.tablespace_name tablespace_name,
          round(a.total_bytes/1024)     total_KB,
          a.no_of_files                 files,
          round(b.free_bytes/1024)      free_KB,
          round((a.total_bytes-b.free_bytes)/1024) used_kb,
          b.fragments                   ,
          round(b.max_frag/1024)        max_frag,
          round(b.min_frag/1024)             min_frag,
          round((a.total_bytes-b.free_bytes)*100/a.total_bytes) pct_used,
          a.lfi,c.tfi
     from (select tablespace_name,
                  sum(bytes) total_bytes,
                  count(bytes) no_of_files,
                  sqrt(max(blocks)/sum(blocks))*
                      (100/sqrt(sqrt(count(blocks)))) lfi
             from sys.dba_data_files
            group by tablespace_name) a,
          (select tablespace_name,
                  sum(bytes) free_bytes,
                  count(bytes) fragments,
                  max(bytes)   max_frag,
                  min(bytes)   min_frag
            from sys.dba_free_space
           group by tablespace_name) b,  
           (select tablespace_name,avg(extents) avg_ext,max(extents) max_ext,
            (1/sqrt(greatest(avg(extents)/5,1))*
            (1/sqrt(greatest(max(extents)/10,1))))*100 tfi
              from sys.dba_segments
      group by tablespace_name) c
 where a.tablespace_name    =b.tablespace_name(+)
   and c.tablespace_name(+) =b.tablespace_name;

spool off
exit
EOF

For More Information

• What do you think about this tip? E-mail the editor at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.