ORA-01404 occurs When changing the length of column using ALTER TABLE MODIFY

Error:

ORA-01404 occurs When changing the length of column using ALTER TABLE MODIFY

Cause:

When a column length is increased, indexes on this column may no longer fulfill the maximum index key size constraint, which is based on the index tablespace block size.

Solution:

The actual solution to this issue is to move the index to a tablespace with a larger block or to redesign the offending indexes to use less columns in the key.

The parameter “_modify_column_index_unusable” is available since DB 12.1.0.1 and can be used to workaround the problem by allowing ALTER TABLE MODIFY to succeed on the cost of making offending indexes unusable. This is, effectively, equivalent to dropping the offending indexes, except that metadata is not lost.

SQL>alter session set “_modify_column_index_unusable”=true;