ORA-14060 or ORA-14265 when modifying column definition (Doc ID 330964.1)

Last updated on AUGUST 31, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.
***Checked for relevance on 29-Aug-2016***
Oracle Server Enterprise Edition - Version: 9.0.1.0 and higher


Symptoms

You are trying to modify a column, for example:

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:

SELECT C.owner,
C.table_name,
C.column_name,
C.data_type,
C.char_length
FROM all_tab_columns C,
all_tables T
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
/

 

Changes

You are trying to change the definition of a column that acts as a (sub)partition key.

Cause

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