Script 1: Index Fragmentation 

  TFSIFRAG.SQL provides information critical in determining whether an  index is a candidate for rebuilding.  An index is a candidate for  rebuilding when a relatively high number of index leaf row deletes have  occured.     Please note: This report does not indicate the actual index "balance."  

 

Script 2: Index Statistics    TFSISTAT.SQL prints a variety of statistics about the given index. Statistics include B*tree height, the number of distinct and repeated keys, the number of  branch rows and blocks, the number of leaf rows and blocks and information about space utilization.     Please note: You will receive an "ORA-01476:  divisor is equal to  zero" for an index on a table into which no rows have been inserted.       Script 3: Index Keys for a Table     TFSINKEY.SQL prints uniqueness, name and column information of indexes for the given table and owner.  The arguments are not case-sensitive, but may not  include wildcards.  The script will behave unreliably for an index not owned  by the owner of its base table.

 

========= Script #1: ========== SET ECHO off REM NAME:   TFSIFRAG.SQL REM USAGE:"@path/tfsifrag schema_name index_name" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM    SELECT on INDEX_STATS REM ------------------------------------------------------------------------ REM PURPOSE: REM    Reports index fragmentation statistics REM ------------------------------------------------------------------------ REM EXAMPLE: REM                     Index Fragmentation Statistic REM                 REM    index name        S_EMP_USERID_UK REM    leaf rows deleted            0 REM    leaf rows in use            25 REM    index badness            0.000   REM  REM ------------------------------------------------------------------------ REM Main text of script follows: set verify off  def ownr  = &&1  def name  = &&2    ttitle -   center 'Index Fragmentation Statistic'   skip 2   set heading off    col name                 newline  col lf_blk_rows          newline  col del_lf_rows          newline  col ibadness newline     validate index &ownr..&name;    select    'index name        '||name,    'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,    'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,	    'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness  from    index_stats  /    undef ownr  undef name  set verify on  ==============Sample Output: ==============                          Index Fragmentation Statistic   index name                   S_EMP_USERID_UK leaf rows deleted            0 leaf rows in use             25 index badness                0.000     ========== Script #2: ========== SET ECHO off REM NAME:   TFSISTAT.SQL REM USAGE:"@path/tfsistat schema_name index_name" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM    SELECT on INDEX_STATS REM ------------------------------------------------------------------------ REM PURPOSE: REM    Report index statistics. REM ------------------------------------------------------------------------ REM EXAMPLE: REM                                Index Statistics  REM REM    S_EMP_USERID_UK  REM    ----------------------------------------------------------  REM    height                          1 REM    blocks                          5 REM    del_lf_rows                     0  REM    del_lf_rows_len                 0 REM    distinct_keys                  25 REM    most_repeated_key               1  REM    btree_space                 1,876 REM    used_space                    447  REM    pct_used                       24 REM    rows_per_key                    1 REM    blks_gets_per_access            2  REM    lf_rows                        25            br_rows               0  REM    lf_blks                         1            br_blks               0 REM    lf_rows_len                   447            br_rows_len           0  REM    lf_blk_len                  1,876            br_blk_len            0   REM  REM ------------------------------------------------------------------------ REM Main text of script follows: set verify offdef ownr        = &&1 def name        = &&2  ttitle -   center  'Index Statistics'  skip 2  set heading off  col name   newline col headsep              newline col height               newline col blocks               newline col lf_rows              newline col lf_blks        	 newline col lf_rows_len          newline col lf_blk_len           newline col br_rows              newline col br_blks              newline col br_rows_len          newline col br_blk_len           newline col del_lf_rows          newline col del_lf_rows_len      newline col distinct_keys        newline col most_repeated_key    newline col btree_space          newline col used_space    	 newline col pct_used             newline col rows_per_key         newline col blks_gets_per_access newline  validate index &ownr..&name;  select   name,   '----------------------------------------------------------'    headsep,   'height               '||to_char(height,     '999,999,990')     height,   'blocks               '||to_char(blocks,     '999,999,990')     blocks,   'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows,   'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,   'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys,   'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key,   'btree_space          '||to_char(btree_space,'999,999,990')       btree_space,   'used_space           '||to_char(used_space,'999,999,990')        used_space,   'pct_used                     '||to_char(pct_used,'990')          pct_used,   'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key,   'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,   'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+   'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows,   'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+   'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks,   'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+   'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len,   'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+   'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len from   index_stats /  undef ownr undef name set verify on  ==============Sample Output: ==============                                 Index Statistics                S_EMP_USERID_UK ----------------------------------------------------------  height                          1  blocks                          5  del_lf_rows                     0  del_lf_rows_len                 0   distinct_keys                  	25  most_repeated_key               1  btree_space                 	1,876 used_space                    	447  pct_used                       	24  rows_per_key                    1  blks_gets_per_access            2  lf_rows                		25         br_rows                 	0 lf_blks				1         br_blks                 	0  lf_rows_len           		447         br_rows_len            		0  lf_blk_len          		1,876         br_blk_len              	0     ========== Script #3: ==========   SET ECHO off REM NAME:   TFSIKEYS.SQL REM USAGE:"@path/tfsikeys idx_owner table_name" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES REM ------------------------------------------------------------------------ REM PURPOSE: REM Shows the index keys for a particular table. REM ------------------------------------------------------------------------ REM EXAMPLE: REM             Index Keys Summary REM REM    Uniqueness                Index Name                    Column Name REM    ---------- ----------------------------------------  ------------------ REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID  REM REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID REM   REM ------------------------------------------------------------------------ REM Main text of script follows: set verify offdef ixowner	= &&1 def tabname	= &&2  ttitle -    center  'Index Keys Summary'  skip 2  col uniq    format a10 heading 'Uniqueness'  justify c trunc col indname format a40 heading 'Index Name'  justify c trunc col colname format a25 heading 'Column Name' justify c trunc  break -   on indname skip 1 -   on uniq  select   ind.uniqueness                  uniq,   ind.owner||'.'||col.index_name  indname,   col.column_name                 colname from   dba_ind_columns  col,   dba_indexes      ind where   ind.owner = upper('&ixowner')     and   ind.table_name = upper('&tabname')     and   col.index_owner = ind.owner      and   col.index_name = ind.index_name order by   col.index_name,   col.column_position /  undef ixowner undef tabname set verify on==============Sample Output: ==============          Index Keys Summary   Uniqueness                Index Name                    Column Name ---------- ---------------------------------------- ---------------------- UNIQUE                SCOTT.S_EMP_ID_PK                        ID                                                                        UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID