Diary of a Confused DBA…..

Here is a small script which shows the index health in your oracle database.

You need to provide your index health threshold and get the result. Then Break Free on those indexes in your database.
Column index_name format A40
Column rowlen format 99999999999999999999
Column leaves format 99999999999999999999
Column net_mb format 999999999999
Column gross_mb format 999999999999
Set Lines 200
– Modify as you require the page size
set pages 5000
Spool index_stat.txt
SELECT
  INDEX_NAME, NUM_ROWS, ROWLEN, LEAVES, NET_MB, GROSS_MB, QUALITY
FROM
 ( SELECT I.INDEX_NAME INDEX_NAME,
   I.NUM_ROWS NUM_ROWS,
   SUM(TC.AVG_COL_LEN + 1) + 7 ROWLEN,
   I.LEAF_BLOCKS LEAVES,
   ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /1000000, 0) NET_MB,
   ROUND(I.LEAF_BLOCKS * (8079 – 23 * NVL(I.INI_TRANS, 2)) * (1 – NVL(I.PCT_FREE, 10) / 100) / 1000000, 0) GROSS_MB,
   ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS / (I.LEAF_BLOCKS * (8079 – 23 * NVL(I.INI_TRANS, 2)) * (1 – NVL(I.PCT_FREE, 10) / 100)) * 100, 0) QUALITY
 FROM
  DBA_INDEXES I, DBA_IND_COLUMNS IC, DBA_TAB_COLUMNS TC
 WHERE I.INDEX_NAME = IC.INDEX_NAME AND
  I.OWNER = IC.INDEX_OWNER AND
  TC.TABLE_NAME = IC.TABLE_NAME AND
  TC.OWNER = IC.INDEX_OWNER AND
  TC.COLUMN_NAME = IC.COLUMN_NAME AND
  I.INDEX_TYPE = ‘NORMAL’ AND
  I.LEAF_BLOCKS > 1000
 GROUP BY I.NUM_ROWS, I.LEAF_BLOCKS, I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
 )
WHERE
  QUALITY <= &threshold_pct
ORDER BY QUALITY;
spool off.