ACCEPT bad_percent  char format a3 default 20 prompt  'Enter bad percent (default 20): '
whenever sqlerror continue;
set termout off
drop table my_index_stats;
create table my_index_stats as select * from index_stats where 1=2;
whenever sqlerror exit;
truncate table my_index_stats;


set pagesize 0;
set linesize 200;
spool index_analyze.sqlplus 
set termout off
select 'analyze index ' || index_name || ' validate structure; '||chr(10)||
'insert into my_index_stats (select * from index_stats);' from user_indexes 
where index_type = 'NORMAL';

spool off
set termout on
prompt Please wait for index analyze... 
set echo off
set termout off
@index_analyze.sqlplus 
commit;
set termout on
set verify off
prompt Candidates for rebuild 

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 ratio heading '% Deleted|Leaf Rows' format 999.99999
set pagesize 200  
SELECT name, del_lf_rows, lf_rows - del_lf_rows lf_rows_used, 
to_char((del_lf_rows /decode(lf_rows, 0, 1, lf_rows))*100,'999.99') ratio FROM my_index_stats where
(to_number(del_lf_rows)/to_number(decode(lf_rows, 0, 1, lf_rows)))*100 >= to_number('&bad_percent')
order by 4
/