ORA-01450 During Online Index Rebuild

This error has nothing to do with the the actual size of the data. The rebuild operation doesn’t actually recreate an index. An online rebuild builds an Index-Organized Table (IOT) in the background to keep track of the changes while an index is being created. That’s how Oracle implements online version of the rebuild. These changes are then merged into the rebuilt index to make it consisten.

The IOT index key is actually bigger than the maximum keylenght, and that’s causing the online index rebuilt to fail. 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.
 
Additional reading on index and IOT internals:


• Metalink Note 136158.1 – ORA-01450, and Maximum Key Length – How it is Calculated.

• Julian Dyke – IOT Internals whitepaper.

Solution:

1) Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2) If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3) Rebuild the index without online clause.

ALTER INDEX index_name REBUILD;