How to resolve ORA-01404 Error Message in Oracle database

Learn the purpose and how to unravel the ORA-01404 error message in Oracle.

Description

When you come upon an ORA-01404 error, the following error message will appear:

ORA-01404: ALTER COLUMN will make a concatenated index too large

Cause

You tried to make bigger the length of a column that is used in an index. This made the concatenated index larger than its most allowed dimension of 255.

The size of the index is calculated as follows:

Date fields are a length of 7. Character fields are their described length. Numeric fields are a size of 22.

The total index size is calculated as the sum of the individual fields as defined above + the quantity of columns in the index.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

You can drop the index and then extend the size of the column. The only problem with this method is that you will no longer be in a position to re-create your index.