HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Rebuild unusable indexes
ORACLE STUFFS
04-Aug-2008
Simple useful script to rebuild unusable indexes (both global and local) in defined scope.

Declare
  p_owner_mask Varchar2(30 char):='^'||USER||'$'; --regular expression owner mask 
  p_table_mask Varchar2(30 char):='.';            --regular expression table mask 
Begin
  for r1 in (  
    Select 'Alter index '||IX.OWNER||'.'||IX.INDEX_NAME||' rebuild '||regexp_replace(PIX.PARTITION_NAME,'(^.+$)','partition \1') as STMT
    from ALL_INDEXES IX
    left join ALL_IND_PARTITIONS PIX on PIX.INDEX_OWNER=IX.OWNER and PIX.INDEX_NAME=IX.INDEX_NAME and IX.PARTITIONED='YES' and PIX.STATUS='UNUSABLE'
    where regexp_like(IX.OWNER,p_owner_mask)
      and regexp_like(IX.TABLE_NAME,p_table_mask)
      and (IX.STATUS='UNUSABLE' or (IX.PARTITIONED='YES' and PIX.INDEX_NAME is not null))
  )loop
     execute immediate r1.STMT;
  end loop;
end;     

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky