ORA-14074 When Adding Table Partitions With Partition Key Defined as Char/Varchar Datatype (Doc ID 1141745.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.1 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

On production database 10.2.0.4 version suddenly when attempting to add a partition to a table using:
 
ALTER TABLE fw_wir.bl_ams_cdr
ADD PARTITION BL_AMS_CDR_20100701
VALUES LESS THAN (TO_DATE(' 2010-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE FW_PROD;

the following error occurs:

ERROR
-----------------------
ORA-14074: partition bound must collate higher than that of the last partition

The table is created using:

CREATE TABLE BL
(
  FW_INSERT_DATE            DATE                NOT NULL,
  FW_COMPONENT              VARCHAR2(8 BYTE)    NOT NULL,
  FW_STATUS                 NUMBER(3),
  FW_SOURCE                 VARCHAR2(32 BYTE),
...
  BAD_FRAME_COUNT           VARCHAR2(32 BYTE),
  EVENT_TIMESTAMP           VARCHAR2(32 BYTE),  <------------------------partition key
...
PARTITION BY RANGE (EVENT_TIMESTAMP)

  PARTITION BL_AMS_CDR_20100302 VALUES LESS THAN (TO_DATE(' 2010-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
...

This is the first time issuing the add partition command on the table.

Changes

Most probably some changes were made at the nls settings level.

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