My Oracle Support Banner

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

Last updated on OCTOBER 10, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.1 [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. 

On production database 10.2.0.4 version suddenly when attempting to add a partition to a table using:
 
ALTER TABLE <user_name>.T1
ADD PARTITION T1_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 TEST
(
  FW_INSERT_DATE            DATE                NOT NULL,
  FW_COMP              VARCHAR2(8 BYTE)    NOT NULL,
  FW_STAT                 NUMBER(3),
  FW_SOURC               VARCHAR2(32 BYTE),
...
  BAD_FR_COUNT           VARCHAR2(32 BYTE),
  EVENT_TIMESTAMP           VARCHAR2(32 BYTE),  <------------------------partition key
...
PARTITION BY RANGE (EVENT_TIMESTAMP)

  PARTITION T1_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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.