Wednesday, May 14, 2008

ORA-14061: data type or length of an index partitioning column may not be changed

We exported and imported Oracle database to Test Environment.
After that, I need to alter column on some tables:

SQL> desc TABLE_NAME

ID_NO varchar2(16)
NAME varchar2(50)

SQL> alter table TABLE_NAME modify (ID_NO varchar2(64))
*
ERROR at line 1:
ORA-14061: data type or length of an index partitioning column may not be changed


I found ID_NO column name, that is an unique constraints (name => ID_UNIQUE):

select CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, INDEX_NAME from user_constraints where TABLE_NAME='TABLE_NAME'

So,
SQL> ALTER TABLE TABLE_NAME disable CONSTRAINT ID_UNIQUE;


SQL> alter table TABLE_NAME modify (ID_NO varchar2(64));
alter tabled.

SQL> ALTER TABLE TABLE_NAME enable CONSTRAINT ID_UNIQUE;

SQL> desc TABLE_NAME

ID_NO varchar2(64)
NAME varchar2(50)

Enjoy!

No comments: