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, 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.