Skip to content

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;

 

No comments yet

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: