ORA-14060 or ORA-14265 when modifying column definition
Last updated on SEPTEMBER 26, 2017
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
***Checked for relevance on 29-Aug-2016***
Oracle Server Enterprise Edition - Version: 126.96.36.199 and higher
You are trying to modify a column, for example:
- changing a CHAR or VARCHAR2 column from BYTE semantics to CHAR semantics (or the other way around)
- changing the length of a column (either a number of of a (var)char column).
- etc. etc.
Because this column is used as a partition key, this change is not allowed and you receive:
ORA-14060: data type or length of a table partitioning column may not be changed
Alternatively when modifying a subpartition key, you would receive:
ORA-14265: data type or length of a table subpartitioning column may not be changed
This is also a possible problem when doing a character set change from single byte to multi byte ( AL32UTF8 typically) or from UTF8 to AL32UTF8 and there are partition keys in the database that contain columns defined with character length semantics .
There cannot be any (sub)partition keys who use CHAR semantics seen the byte lenght "behind the scene" changes during the character set change and the alter database/Csalter or DMU tool will fail.
This select will give all partitioned tables using char semantics columns:
FROM all_tab_columns C,
WHERE C.owner = T.owner
AND C.table_name = T.table_name
AND C.char_used = 'C'
AND T.PARTITIONED ='YES'
AND C.table_name NOT IN
(SELECT table_name FROM all_external_tables
AND C.data_type IN ('VARCHAR2', 'CHAR')
ORDER BY 1,2
You are trying to change the definition of a column that acts as a (sub)partition key.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms