ORA-01450: maximum key length (string) exceeded

I got ” ORA-01450: maximum key length (string) exceeded ”  error in Oracle database.

ORA-01450: maximum key length (string) exceeded

Details of error are as follows.

ORA-01450: maximum key length (string) exceeded

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.

maximum key length (string) exceeded

This ORA-01450 error is related with 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.

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

When creating an Index, the total length of the index cannot exceed a certain value. This value depends primarily on the DB_BLOCK_SIZE. If an attempt is made to create an index larger than the Maximum value, an ORA-1450 is raised:

ORA-01450 maximum key length (758) exceeded ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

The number in parends is the maximum allowable length of the index key for that particular system.

So, how is this number calculated?

The maximum key size means:

The total index length + length of the key (2 Bytes) + ROWID (6 Bytes) + the length of the rowid (1 byte).

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. For Funtion-based indexes, we must calculate the length of the return type.

This index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block.

Therefore, the maximum key length for an index will be less than half of the DB_BLOCK_SIZE.  the maximum size of a single index entry is approximately one-half the data block size. However, when considering that we must also leave space in the block according to PCTFREE, INITRANS, and space for block overhead (Block Header, ROW Directory, Table Directory, etc) the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Using default values for these storage options, the maximum length for indexes is as follows for different block sizes:

DB_BLOCK_SIZE: Maximum Index Key Length:
============== =========================

2K (2048) 758 Bytes
4K (4096) 1578 Bytes
8K (8192) 3218 Bytes
16K (16384) 6498 Bytes

If you hit a maximum key length in an index according to the DB_BLOCK_SIZE, you may need to recreate the database with a larger block size. The other alternative is to limit the size of the index. This is slightly more difficult with a Function-based index, when the return type is a varchar or RAW.

To limit the size of a function-based index you should consider using the SUBSTR or SUBSTRB function, to limit the number of Characters or Bytes returned. For more information on SUBTR and SUBSTRB, refer to the

To solve this error, create 16k blocksize tablespace and use this tablespace for table and index.

SQL> create tablespace TBS_16K datafile '+DATAC1' size 100M blocksize 16k;

Tablespace created.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )