* RebuildUnusableIndexes.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!

Column cnt New_Value _cnt FORMAT A12
COLUMN txt       FORMAT A200

set echo off termout off 
Select LTRIM(TO_CHAR(count(*),'999,999,999')) cnt from all_indexes where status='UNUSABLE';
set termout on
PROMPT Rebuild Unusable Indexes (there are currently &&_cnt unusable indexes)

set heading off PAGES 0 TRIMSPOOL ON feedback off Verify Off ECHO off
ACCEPT index_owner PROMPT "Enter owner of the unusable indexes to rebuild (% for all): "
ACCEPT index_name PROMPT "Enter the name of the unusable index to be rebuilt (% for all): "

  'ALTER INDEX '||o.owner||'.'||o.INDEX_name||' REBUILD;' txt
where o.owner like UPPER('&&index_owner')
and (o.owner NOT IN ('SYS','SYSTEM'))
and o.index_name like UPPER('&&index_name')
and o.status = 'UNUSABLE'

SPOOL RebuildUnusableIndexes.TMP

spool off
set feedback on heading On PAGES 24 Verify On

undef type
undef owner
undef object_name
undef _cnt

Clear Col

Prompt You now have a file called RebuildUnusableIndexes.TMP to run...

Set Doc Off

UNDEF index_owner
undef index_name
Set Doc On

/* SUMMARY - Identify indexes marked as UNUSABLE and generate a script to rebuild them so as to fix the issue
 * DETAIL - Oracle indexes can become "unusable" for a number of reasons. For instance, if you re-organise a table using
 * ALTER TABLE xxx MOVE, then this involves row movement and as a B-Tree index is a structure with pointers to table ROWIDs
 * then the original B-Tree pointers will no longer be valid/accurate - hence Oracle marks the index as being UNUSABLE.
 * Another example would be when certain bulk loading methods - such as SQL*Loader direct path load - are used where data is pre-formatted
 * in to data blocks and simply appended to the table directly. No effort is made in these tools to format and append the index blocks.
 * This script very quickly identifies any index that is marked as UNUSABLE - irrespective of how it got into that state - and creates a text file
 * of the ALTER INDEX ... REBUILD statements required to make the indexes usable able. It gives a count of total number of UNUSABLE indexes  in the database
 * and allows index owner and name to be entered as select parameters (with wild-card matching allowed). SYS and SYSTEM indexes are ignored however.
 * Note that if you do have unusable indexes in the database and are unable to rebuild them for whatever reason then it is possible to prevent any query from 
 * attempting to use these indexes by setting the SKIP_UNUSABLE_INDEXES parameter.
Sample output might look like:
HR@RACDB> start rebuildunusableindexes

HR@RACDB> alter table employees move;

Table altered.

HR@RACDB> start rebuildunusableindexes

Rebuild Unusable Indexes (there are currently 6 unusable indexes)

Enter value for index_owner: HR
Enter value for index_name: %

You now have a file called RebuildUnusableIndexes.TMP to run...

HR@RACDB> start RebuildUnusableIndexes.TMP

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

 * (c) Tim Onions 2007
 * Script used at the database owners own risk.