ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

If any index status is unusable then we will have to identify the same and rebuilt as given below.

SQL>SELECT OWNER, INDEX_NAME,'' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' UNION SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE' UNION SELECT INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME FROM dba_ind_subpartitions WHERE STATUS = 'UNUSABLE';

Suppose here we will try to rebuild partition index, then we can face error as "ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index".

Here we can see the index 'EGO_MTL_SY_ITEMS_EXT_TL_N1' have sub-partitioned index.

SQL>select 'alter index '||index_owner||'.'||index_name|| ' rebuild partition '||partition_name||' online compute statistics;' from dba_ind_partitions where index_name ='EGO_MTL_SY_ITEMS_EXT_TL_N1';

SQL> alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild partition AG_ZERO online compute statistics;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild partition AG_ZERO online compute statistics

                *

ERROR at line 1:

ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

Solution:-

Check the sub-partition index and rebuild the sub-partion index instead of partitioned index.

Check out the index details.

SQL> select index_name name,partition_name partition,subpartition_name Subpartition,status from all_ind_subpartitions i where   I.INDEX_NAME='EGO_MTL_SY_ITEMS_EXT_TL_N1';

NAME PARTITION SUBPARTITION STATUS

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_ZERO AG_ZERO_DL1 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_ZERO AG_ZERO_DL2 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_ZERO AG_ZERO_DL3 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_ZERO AG_ZERO_DL4 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_ZERO AG_ZERO_DL5 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_ZERO AG_ZERO_DL6 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_1 AG_1_DL1 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_1 AG_1_DL2 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_1 AG_1_DL3 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_1 AG_1_DL4 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_1 AG_1_DL5 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_1 AG_1_DL6 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_2 AG_2_DL1 UNUSABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_2 AG_2_DL2 UNUSABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_2 AG_2_DL3 UNUSABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_2 AG_2_DL4 UNUSABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_2 AG_2_DL5 UNUSABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_2 AG_2_DL6 UNUSABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_121 AG_121_DL1 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_121 AG_121_DL2 USABLE

EGO_MTL_SY_ITEMS_EXT_TL_N1 AG_121 AG_121_DL3 USABLE

SQL> select 'alter index '||index_owner||'.'||index_name|| ' rebuild subpartition '||Subpartition_name||' online ;' from dba_ind_subpartitions where index_name ='EGO_MTL_SY_ITEMS_EXT_TL_N1';

'ALTERINDEX'||INDEX_OWNER||'.'||INDEX_NAME||'REBUILDSUBPARTITION'||SUBPARTITION_NAME||'ONLINE;'

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_ZERO_DL2 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_ZERO_DL3 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_ZERO_DL4 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_ZERO_DL5 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_ZERO_DL6 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_1_DL1 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_1_DL2 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_1_DL3 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_1_DL4 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_1_DL5 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_1_DL6 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_2_DL1 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_2_DL2 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_2_DL3 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_2_DL4 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_2_DL5 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_2_DL6 online ;

alter index EGO.EGO_MTL_SY_ITEMS_EXT_TL_N1 rebuild subpartition AG_121_DL1 online ;

Once rebuild the index, verify the indexes as below.

[appltest@***test scripts]$ sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Jan 25 12:02:00 2022

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT OWNER, INDEX_NAME,'' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' UNION SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE' UNION SELECT INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME FROM dba_ind_subpartitions WHERE STATUS = 'UNUSABLE';

no rows selected

SQL>