ORA-14060: data type or length of a table partitioning column may not be changed

I got ” ORA-14060: data type or length of a table partitioning column may not be changed ” error in Oracle database during startup 2 nodes Oracle RAC.

ORA-14060: data type or length of a table partitioning column may not be changed

Details of error are as follows.

ALTER TABLE MSDBA.PERSON_GSM MODIFY GSM_NO VARCHAR2(20 Byte)
Error at line 3
ORA-14060: data type or length of a table partitioning column may not be changed

The ORA-14060  error is related with the definition of a column that acts as a (sub)partition key.

There are 2 ways of resolving this issue, the first one is basically just exporting the data and dropping the table, if needed do the characterste change, then recreating the table and importing the data.
In some cases that can be too time consuming, and therefore this note will give a quicker solution, even though it is more labour intensive (in the below text we use “partitions”, but when you hit ORA-14265 then you can use the same method for subpartitions).
When going to AL32UTF8 the solutions is to go to BYTE semantics BEFORE the characterset conversion and then, after the characterset change , go back to CHAR semantics.

The flow is :

• create dummy tables exactly like the partitioned table, one for every partition, and leave these tables empty
• drop the triggers and constraints on the partitioned table
• disable any referential integrity constraints on other tables pointing to the partitioned table
• exchange all the partitions with their corresponding “dummy” tables – this is a very simple data dictionary operation that does not involve the movement of any data
• drop the partitioned table (all the data now lives in the “dummy tables”, so nothing is lost)
• recreate the partitioned table with the desired new column sizes and/or length semantics
• modify the column(s) in the “dummy” tables to be the same as the newly recreated partitioned table
• exchange the partitions back into the partitioned table (again a simple data dictionary operation – no actual movement of data)
• recreate the triggers and constraints as they were on the original table
• re-enable any constraints that were disabled in the 3rd step
• drop the dummy tables which are now empty again

This can of course all be scripted before it is actually done, so when it is actually performed it can be done very quickly with minimal downtime.
To make the re-creation of the constraints easier please see note 1016836.6 on capturing the constraints into a sql script (before dropping them). In case there are very many partitions a version of the script in Note 313175.1 can be used to modify all the tables after they have been “swapped out”.

The following is a full example in which we change a VARCHAR2 column from using BYTE semantics to using CHAR semantics, but the example works in exactly the same way if you try to simply change a column size or go from CHAR to BYTE.
Note: when going from CHAR to BYTE to do a conversion to AL32UTF8 you need to use the column size in CHARacters*3 as the new BYTE length. Once on AL32UTF8 you then can go back to the orginial CHAR definition.

SQL> DROP TABLE EMP2
2 /

Table dropped.

SQL> CREATE TABLE emp2
2 (empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 sal NUMBER(7))
5 PARTITION BY RANGE(ename)
6 (partition emp2_p1 VALUES LESS THAN ('D'),
7 partition emp2_p2 VALUES LESS THAN ('Q'),
8 partition emp2_p3 VALUES LESS THAN (MAXVALUE))
9 /

Table created.

SQL> insert into emp2 values(1,'Andy',575)
2 /

1 row created.

SQL> insert into emp2 values(2,'Derek',570)
2 /

1 row created.

SQL> insert into emp2 values(3,'Patrick',590)
2 /

1 row created.

SQL> insert into emp2 values(4,'Richard',400)
2 /

1 row created.

SQL> -- Lets see what's in there:
SQL> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7)

SQL> select * from emp2;

EMPNO ENAME SAL
---------- ---------- ----------
1 Andy 575
2 Derek 570
3 Patrick 590
4 Richard 400

SQL> select * from emp2 partition (emp2_p1);

EMPNO ENAME SAL
---------- ---------- ----------
1 Andy 575

SQL> select * from emp2 partition (emp2_p2);

EMPNO ENAME SAL
---------- ---------- ----------
2 Derek 570
3 Patrick 590

SQL> select * from emp2 partition (emp2_p3);

EMPNO ENAME SAL
---------- ---------- ----------
4 Richard 400

SQL> -- try the modify, expecting ORA-14060:
SQL> alter table emp2 modify(ename varchar2(10 char))
2 /
alter table emp2 modify(ename varchar2(10 char))
*
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be
changed


SQL> -- Set up the dummy swap tables
SQL> create table emp2_part1
2 (empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 sal NUMBER(7))
5 /

Table created.

SQL> create table emp2_part2
2 (empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 sal NUMBER(7))
5 /

Table created.

SQL> create table emp2_part3
2 (empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 sal NUMBER(7))
5 /

Table created.

SQL> -- exchange the partitions with the empty swap tables:
SQL> alter table emp2 exchange partition emp2_p1 with table emp2_part1
2 /

Table altered.

SQL> alter table emp2 exchange partition emp2_p2 with table emp2_part2
2 /

Table altered.

SQL> alter table emp2 exchange partition emp2_p3 with table emp2_part3
2 /

Table altered.

SQL> -- Now have a look at the current situation
SQL> -- now we expect an empty emp2 and filled tables that were exchanged out:
SQL> select * from emp2;

no rows selected

SQL> select * from emp2 partition (emp2_p1);

no rows selected

SQL> select * from emp2 partition (emp2_p2);

no rows selected

SQL> select * from emp2 partition (emp2_p3);

no rows selected

SQL> select * from emp2_part1;

EMPNO ENAME SAL
---------- ---------- ----------
1 Andy 575

SQL> select * from emp2_part2;

EMPNO ENAME SAL
---------- ---------- ----------
2 Derek 570
3 Patrick 590

SQL> select * from emp2_part3;

EMPNO ENAME SAL
---------- ---------- ----------
4 Richard 400

SQL> -- Now drop the empty emp2 and re-create it with CHAR semantics:
SQL> DROP TABLE EMP2
2 /

Table dropped.

SQL> CREATE TABLE emp2
2 (empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10 CHAR),
4 sal NUMBER(7))
5 PARTITION BY RANGE(ename)
6 (partition emp2_p1 VALUES LESS THAN ('D'),
7 partition emp2_p2 VALUES LESS THAN ('Q'),
8 partition emp2_p3 VALUES LESS THAN (MAXVALUE))
9 /

Table created.

SQL> -- And modify the tables in which the data now lives:
SQL> alter table emp2_part1 modify(ename varchar2(10 CHAR))
2 /

Table altered.

SQL> alter table emp2_part2 modify(ename varchar2(10 CHAR))
2 /

Table altered.

SQL> alter table emp2_part3 modify(ename varchar2(10 CHAR))
2 /

Table altered.

SQL> -- And Finlay we can exchange tables that hold the data back into the main table:
SQL> alter table emp2 exchange partition emp2_p1 with table emp2_part1
2 /

Table altered.

SQL> alter table emp2 exchange partition emp2_p2 with table emp2_part2
2 /

Table altered.

SQL> alter table emp2 exchange partition emp2_p3 with table emp2_part3
2 /

Table altered.

SQL> -- Cleanup
SQL> drop table emp2_part1
2 /

Table dropped.

SQL> drop table emp2_part2
2 /

Table dropped.

SQL> drop table emp2_part3
2 /

Table dropped.

SQL> -- And check that everything is the same as before, except ename is now CHAR:
SQL> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10 CHAR)
SAL NUMBER(7)

SQL> select * from emp2;

EMPNO ENAME SAL
---------- ---------- ----------
1 Andy 575
2 Derek 570
3 Patrick 590
4 Richard 400

SQL> select * from emp2 partition (emp2_p1);

EMPNO ENAME SAL
---------- ---------- ----------
1 Andy 575

SQL> select * from emp2 partition (emp2_p2);

EMPNO ENAME SAL
---------- ---------- ----------
2 Derek 570
3 Patrick 590

SQL> select * from emp2 partition (emp2_p3);

EMPNO ENAME SAL
---------- ---------- ----------
4 Richard 400

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )