Problem solve Get help with specific problems with your technologies, process and projects.

Unix script to gather tablespace information

This Unix script is used to gather tablespace information, including space, used_space and fragmentation.

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,
     from (select tablespace_name,
                  sum(bytes) total_bytes,
                  count(bytes) no_of_files,
                      (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(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

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.

Dig Deeper on Oracle database design and architecture