ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

ORA-01450 might crop up when a table index is being created in the database.

ORA-01450: maximum key length (6398) exceeded

Oracle documentation has this to say about the error:

Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system.

The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns.

Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.

Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system.

The action suggests choosing index columns differently so as to remain within the index length limit.

Things are not always so simple though. When faced with this error while installing Oracle’s standard products such as FMW components or OBIEE, it is not in one’s hands to follow this advice and fiddle with the index columns.

How does one fix this error, then? This post suggests possible root causes and solution for ORA-01450 when changing the index itself is not a viable option.

1. DB Block Size

A common cause of ORA-01450 is the DB block size not being large enough for the index.

To check the DB block size, login as system user and show parameter value of db_block_size.

SQL> show parameter DB_BLOCK_SIZE

NAME TYPE VALUE
------------------------------------ ----------- -----
db_block_size integer 8192

Oracle restricts the index key to about 3/4th of the DB block size. So with a db_block_size of 8192 (that’s the default, by the way) you can have a maximum key length of 6398. If you MUST have a key length that exceeds that limit, increasing the db_block_size to accommodate the index can resolve the error.

2. Character Encoding

Oracle products generally recommend AL32UTF8 as character set for the Oracle database used as their repository (example).

If you run Oracle RCU (Repository Creation Utility) on a non-AL32UTF8 database , a warning appears:

The database you are connecting is with non-AL32UTF8 character set. Oracle strongly recommends using AL32UTF8 as the database character set.

Though the documentation suggests that

You can ignore this warning and continue using RCU

, a non-AL32UTF8 character set is known to cause the "ORA-01450: maximum key length exceeded" error.

If you are seeing ORA-01450 in a non-AL32UTF8 database, consider changing the character set to AL32UTF8.

To find out the current character set, select from nls_database_parameters.

SQL> select * from nls_database_parameters
2 where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ -------------
NLS_CHARACTERSET WE8MSWIN1252

Caution: It is important to analyse and address the impact if you decide to change the database character set after database creation. Refer to Oracle Globalization Support Guide [changing the database character set (12.1)] for details.

3. NLS Length Semantics

NLS Length Semantics may be set to BYTE (the default) or CHAR (refer this Oracle-base article or Oracle Globalization Support Guide for more details), but many Oracle FMW products have something like this to say:

Oracle Fusion Middleware only supports schemas in a byte-mode database. The nls_length_semantics initialization parameter on the database where the schemas reside must be set to BYTE; setting this parameter to CHAR is not supported.

(source: GoldenGate Monitor v12 installation guide)

An incompatible NLS Length Semantics setting is a common cause of "ORA-01450: maximum key length exceeded" in Oracle product installations.

To check the NLS length semantics in your database, login as system user and show parameter value of nls_length_semantics.

SQL> show parameters nls_length_semantics
NAME TYPE VALUE
------------------------------------ ----------- -----
nls_length_semantics string BYTE

Changing the initialization parameter from CHAR to BYTE and restarting the database can resolve the error.

Note:After you change a database parameter, remember to bounce the database to let the changed parameter take effect and then attempt to create the index again.

Summary

This article puts forth three probable causes, and their fixes, for the Oracle error ORA-01450: maximum key length exceeded, when changing the index key itself is not a viable option. It recommends verifying the values of the following in the Oracle database:

1. DB Block Size
2. Character Encoding
3. NLS Length Semantics

For more on classic Oracle errors, check out articles in the category ORA Errors.