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

ID_NO varchar2(16)
NAME varchar2(50)

SQL> alter table TABLE 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'

So,
SQL> ALTER TABLE TABLE disable CONSTRAINT ID_UNIQUE;


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

SQL> ALTER TABLE TABLE enable CONSTRAINT ID_UNIQUE;

SQL> desc TABLE

ID_NO varchar2(64)
NAME varchar2(50)

Enjoy!

0 comments: