Error while altering the table column size
ALTER TABLE TEST_TABLE MODIFY COL_N_NUMBER VARCHAR2(13)
Error report -
SQL Error: ORA-14061: data type or length of an index partitioning column may not be changed
14061. 00000 - "data type or length of an index partitioning column may not be changed"
*Cause: User issued ALTER TABLE statement attempting to modify
data type and/or length of a column used to partition some index
defined on the table named in ALTER TABLE statement, which is
illegal
*Action: Avoid modifying data type and/or length of index
partitioning column(s)
Solution:
a) Check the constraint or index on the underlying column to be modified
select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,INDEX_NAME , status from user_constraints where TABLE_NAME='TEST_TABLE'
We found that there is a unique constraint (constraint_type='U') on column COL_N_NUMBER. So first we need to disable it before modifying column size.
b) Get the Unique Constraint Name (constraint_type='U') and disable it first
SQL> alter table TEST_TABLE disable constraint TESTI_TABLE_1
Table altered.
c) Modify the column size
SQL> ALTER TABLE KCA_PART_DETAILS MODIFY COL_N_NUMBER VARCHAR2(13 CHAR)
Table altered.
d) Enable constraint back
SQL> alter table KCA_PART_DETAILS enable constraint TESTI_TABLE_1
Table altered.
