Index Fragmentation

set heading off
set feedback off
spool index_analyze.sql
select 'analyze index '|| object_name ||' validate structure; ' || chr(13) || chr(10) || 
'insert into custom_user.index_stat_bk(NAME,LF_ROWS,DEL_LF_ROWS) select NAME,LF_ROWS,DEL_LF_ROWS from index_stats;' from dba_objects where object_type='INDEX' and owner='APPS';
spool off
set heading on
set feedback on
@index_analyze.sql




select name,(DEL_LF_ROWS/LF_ROWS)*100  from index_stat_bk where DEL_LF_ROWS >0 and LF_ROWS>0;