How Do Indexes Become Unusable

Maintenance operations causing index partitions to become INDEX UNUSABLE (IU):

   1. IMPORT PARTITION or conventional path SQL*Loader.

   2. Direct-path SQL*Loader leaves affected local index partitions and  global indexes in an IU state if it does not complete successfully.

   3. Partition maintenance operations like ALTER TABLE MOVE PARTITION.

   4. Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION.

   5. Partition maintenance operations like ALTER TABLE SPLIT PARTITION.

   6. Index maintenance operations like ALTER INDEX SPLIT PARTITION.

   7. Adding a Partition to a Hash-Partitioned Table

Adding a Partition to a Hash-Partitioned Table

When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.

The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear ADD PARTITION p_named TABLESPACE gear5;

Indexes may be marked UNUSABLE as explained in the following table:

Table Type               Index Behavior
Regular (Heap)         Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
                              The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and                               must be rebuilt.
                              All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.


Index-organized       For local indexes, the behavior is identical to heap tables.
                              All global indexes remain usable.

Each of these operations may cause index partitions to be marked IU which will require the index partitions to be rebuilt.

Version 10.2
=============
By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then
rebuild the entire index or, in the case of a global index, each of its partitions. The database lets you override this default behavior if you specify
UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index at the time it
executes the maintenance operation DDL statement.

The following operations support the UPDATE INDEXES clause:
    *  ADD PARTITION | SUBPARTITION
    *  COALESCE PARTITION | SUBPARTITION
    *  DROP PARTITION | SUBPARTITION
    *  EXCHANGE PARTITION | SUBPARTITION
    *  MERGE PARTITION | SUBPARTITION
    *  MOVE PARTITION | SUBPARTITION
    *  SPLIT PARTITION | SUBPARTITION
    *  TRUNCATE PARTITION | SUBPARTITION

Reference : Oracle support metalink