Wednesday, May 2, 2012

Unusable State of Index

Unusable State of Index


Unusable state of indexes will cause poor performance of database.

How to find Unusable Index:
select Index_name from dba_indexes where status='UNUSABLE'

How to make index Usable:
alter index  <index name> rebuild

How to create above alter statement dynamically:
select  'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE'


No comments: