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.