ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded

ISSUE:

SQL> select name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio from index_stats where name like 'FND_LOG_MESSAGES_N8';

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
------------------------------ ---------- ---------- ----------- ----------
FND_LOG_MESSAGES_N8                     3    7729513     6882517 89.0420522

SQL> alter index APPLSYS.FND_LOG_MESSAGES_N8 rebuild online;
alter index APPLSYS.FND_LOG_MESSAGES_N8 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

SOLUTION:

SQL> alter index APPLSYS.FND_LOG_MESSAGES_N8 rebuild;

Index altered.

SQL> select name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio from index_stats where name like 'FND_LOG_MESSAGES_N8';

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
------------------------------ ---------- ---------- ----------- ----------
FND_LOG_MESSAGES_N8                     3     847078           0          0

ANALYSIS:

The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.