Index Rebuild Online Failed Oracle 11gR2: ORA-01450: maximum key length (3215) exceeded

_____________________________________________________________________________________________________________________

Problem Description: Index creation or rebuild with online clause failed with following error in Oracle 11gR2.

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

SQL> alter index SCOTT.M_DEPART_A1 rebuild online;

alter index SCOTT.M_DEPART_A1 rebuild online

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

SQL> CREATE INDEX SCOTT.M_DEPART_A1 ON SCOTT.M_SCOART  (SCO_PARENT_DOCNAME, SCO_PARENT_LOCALREF, SCO_HIGH_CN, SCO_PARTITION_ID, SCO_TOCHILD_ROLE) online;

CREATE INDEX SCOTT.M_DEPART_A1 ON SCOTT.M_DEPART  (SCO_PARENT_DOCNAME, SCO_PARENT_LOCALREF, SCO_HIGH_CN, SCO_PARTITION_ID, SCO_TOCHILD_ROLE) online

                                                        *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

Solution Description:

Index rebuild online command internally creates a journal table to track of inserts and updates. This journal table size is limited to particular extent and you get this error when it crosses.

In this particular situation you have to rebuild your index without online clause.

SQL>   CREATE INDEX SCOTT.M_DEPART_A1 ON SCOTT.M_DEPART  (SCO_PARENT_DOCNAME, SCO_PARENT_LOCALREF, SCO_HIGH_CN, SCO_PARTITION_ID, SCO_TOCHILD_ROLE);

Index created.

SQL>  alter index SCOTT.M_DEPART_A1 rebuild;

Index altered.

_____________________________________________________________________________________________________________________

Website Stats