ANALYZE INDEX &&index_name VALIDATE STRUCTURE; 
 
COL name         HEADING 'Index Name'          FORMAT a30 
COL del_lf_rows  HEADING 'Deleted|Leaf Rows'   FORMAT 99999999 
COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999 
COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999 
 
SELECT
    name
  , del_lf_rows
  , lf_rows - del_lf_rows lf_rows_used
  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness 
FROM   index_stats
/
 
prompt 
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt 

undefine index_name