SQL Error: ORA-14086: a partitioned index may not be rebuilt as a whole /ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

We are facing one issue while loading   table in pre-modeling server our load get failed. Because An index is in unusable state .Please rebuild the index.

SQL> alter index INDEX_NAME rebuild
Error report -
SQL Error: ORA-14086: a partitioned index may not be rebuilt as a whole
14086. 00000 - "a partitioned index may not be rebuilt as a whole"
*Cause: User attempted to rebuild a partitioned index using
ALTER INDEX REBUILD statement, which is illegal
*Action: Rebuild the index a partition at a time (using
ALTER INDEX REBUILD PARTITION) or drop and recreate the
entire index

Reason: This is a partition index you can not directly rebuild or rebuild as a whole.

Solution: Check what are the partitioned index for that Index.

Step 1: SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='INDEX_NAME';

INDEX_NAME                     PARTITION_NAME                 STATUS 

--------------------------------------------------------------------------------------------------------------------------

DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A      
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A      
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A       
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A       
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A      
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A      
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A       
DWH_ZVN _IDX            DWH_ZVN_RLP_2012_Q2            N/A       

Step 2: Rebuild the partioned index.

SQL> alter session set current_schema=SCHEMA_NAME;

SQL> alter index DWH_ZVN _IDX rebuild partition DWH_ZVN_RLP_2012_Q2 tablespace TEMP_DWH_DYNI;

ERROR at line 1:
ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

The index has a subpartition also. With a SubPartitioned Index, the Partitions do NOT have segments.  It is only the SubPartitions that have segments that can be rebuilt.

Step 3: Check what are the subpartitioned index and their status.

SQL>select index_name, partition_name, subpartition_name,status from dba_ind_subpartitions where index_name='index_name';

You might get huge no of subpartitions that need to be rebuild.To rebuild all the subpatitions at a time follow the below seps.

Step4: SQL> spool rebuild_index.sql

SQL> select 'alter index INDEX_NAME rebuild subpartition '||"SUBPARTITION_NAME"||' ;' from dba_ind_subpartitions where index_name='INDEX_NAME';

SQL> spool off;

Step5: @rebuild_index.sql

Check all the index status hopefully it is rebuild.