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>

ORA-00020: maximum number of processes 150 exceeded

If any one getting below error in database alert log file. In this case we need to increase the process in parameter file(pfile or spfile). 

Alert log:-

ORA-00020: maximum number of processes 150 exceeded

ORA-20 errors will not be written to the alert log for

 the next minute. Please look at trace files to see all

 the ORA-20 errors.

To check the current configuration in database. 

SQL>select value from v$parameter where name = 'processes';

VALUE

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

150

or 

SQL>show parameter process;

SQL>show parameter sessions

SQL>show parameter transactions

Note:- Before change "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters.

  

Formula for determining  these parameter values is as follows:

   

        processes=x

        sessions=x*1.1+5

        transactions=sessions*1.1

if  process=500

    sessions=500*1.1+5  = 555

    transactions=sessions*1.1 = 610

To resolve 'ORA-00020: maximum number of processes 150 exceeded' need to be increase the processes value in oracle database. Do the same as below.

SQL> alter system set processes=500 scope=spfile;

System altered.

Once alter the processes value, need to be restart the oracle database to effect the changes.

SQL>shutdown immediate

SQL>startup

In some situation we don't want to bounce the database. To resolve the this issue temporarily we need to kill inactive session.

SQL>select 'kill -9 ' || p.SPID, s.USERNAME, 'alter system kill session '''||sid||',' || s.serial# || ''';',s.STATUS,s.logon_time,s.terminal,s.machine

from v$session s, v$process p

where s.PADDR = p.ADDR (+)

and s.STATUS='INACTIVE' 

order by 1;