Skip to content

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


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


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.


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 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;


No comments yet

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: