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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google