/* * I have found how identify index fragmentation, you have to call * ANALYZE INDEX VALIDATE STRUCTURE sql-statement, after that look in INDEX_STATS view * where is last analyze cmd report * * Fragmentation is ratio del_lf_rows/lf_rows (in attachment) * http://dbataj.blogspot.com/2007/11/index-fragmentation-rebuild.html * http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4216.htm#sthref2013| */ set linesize 400 set pages 999 set heading off set linesize 120 set feedback off col owner for a20 col name for a40 spool analyze.sql SELECT ' ANALYZE INDEX '|| owner || '.' || index_name || ' VALIDATE STRUCTURE;' ||chr(10)|| ' SELECT '''||owner||''' owner, name, ' || CHR(10) || ' TRUNC(100*(del_lf_rows/(CASE WHEN lf_rows=0 THEN -1 ELSE lf_rows END)),2) fragment_pct ' || CHR(10) || ' from index_stats;' from dba_indexes where owner IN ('MY_SCHEMA') order by owner, index_name;