Thursday, July 13, 2017

Possible solution to ORA-01450: maximum key length (3800) exceeded when rebuilding an index

As a part of disabling TDE in a test database, I was moving indexes out of the TDE encrypted tablespace to another, similarly created tablespace, but one without encryption.

When trying to execute

ALTER INDEX SCOTT.MYTABLE_U01 REBUILD ONLINE TABLESPACE DATA32K_NOTDE
one of the tables returned an error during the online rebuild:

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

This error is normally worked around by one of the following actions:

1. Rebuild the database with a larger blocksize
2. Add a new tablespace with a larger blocksize (my preferred solution)
3. Make the index smaller, meaning drop and recreate the index and omit one or more of the previously indexed columns


This is documented in Doc ID 747107.1 "ORA-01450 Error on Create Index" at Oracle Support.

For me though, it worked by simply changing

REBUILD ONLINE 

to

REBUILD

and the index rebuild executed without problems.
The Oracle Documentation for 12cR1 has a few restrictions regarding the use of online rebuilds, but none of them seems relevant to the error I observed.

For more information about the ALTER INDEX statement, see the official documentation from Oracle

No comments:

Post a Comment