Oracle ORA-14074 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

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

ORA-14074 is a common Oracle error encountered when working with partitioned tables. It occurs when you attempt to add a new partition whose boundary value is less than or equal to the boundary value of the existing last partition. Oracle enforces a strict ascending order rule for Range partition bounds, and any violation of this rule immediately raises this error.

Top 3 Causes

1. Adding a Partition with a Lower or Equal Boundary Value

The most frequent cause is specifying a VALUES LESS THAN clause that does not exceed the current last partition's upper bound.

-- Check existing partition boundaries first
SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'SALES'
ORDER BY PARTITION_POSITION;

-- WRONG: Last partition ends at 2024-01-01, trying to add 2023-06-01
ALTER TABLE SALES
ADD PARTITION P202306
VALUES LESS THAN (TO_DATE('2023-06-01', 'YYYY-MM-DD'));
-- ORA-14074 occurs here

-- CORRECT: Use a value higher than the current last partition bound
ALTER TABLE SALES
ADD PARTITION P202407
VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'));

2. Defining Partitions Out of Order at Table Creation

When creating a partitioned table, all partition bounds must be defined in strictly ascending order. Accidentally placing a larger bound before a smaller one during CREATE TABLE will trigger ORA-14074.

-- WRONG: Partitions defined in wrong order
CREATE TABLE SALES (
    SALE_ID   NUMBER,
    SALE_DATE DATE,
    AMOUNT    NUMBER
)
PARTITION BY RANGE (SALE_DATE) (
    PARTITION P2024 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD')),
    PARTITION P2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')), -- Error!
    PARTITION P2022 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD'))
);

-- CORRECT: Partitions in ascending order
CREATE TABLE SALES (
    SALE_ID   NUMBER,
    SALE_DATE DATE,
    AMOUNT    NUMBER
)
PARTITION BY RANGE (SALE_DATE) (
    PARTITION P2022 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
    PARTITION P2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
    PARTITION P2024 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD'))
);

3. Attempting to ADD PARTITION When MAXVALUE Already Exists

If the last partition uses VALUES LESS THAN (MAXVALUE), no additional partition can be added using ADD PARTITION. MAXVALUE represents positive infinity, so no bound can exceed it.

-- WRONG: Trying to add a partition after MAXVALUE exists
ALTER TABLE SALES
ADD PARTITION P202407
VALUES LESS THAN (TO_DATE('2024-07-01','YYYY-MM-DD'));
-- ORA-14074 occurs

-- CORRECT: Use SPLIT PARTITION instead
ALTER TABLE SALES
SPLIT PARTITION P_MAX
AT (TO_DATE('2024-07-01','YYYY-MM-DD'))
INTO (
    PARTITION P202407,
    PARTITION P_MAX
);

Quick Fix Solutions

Always query USER_TAB_PARTITIONS before adding a partition to confirm the current highest bound.
Use SPLIT PARTITION when a MAXVALUE partition already exists instead of ADD PARTITION.
Consider Interval Partitioning (Oracle 11g+) to automate partition creation and avoid manual boundary management entirely.

-- Interval Partitioning: auto-creates monthly partitions
CREATE TABLE SALES_AUTO (
    SALE_ID   NUMBER,
    SALE_DATE DATE,
    AMOUNT    NUMBER
)
PARTITION BY RANGE (SALE_DATE)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION P_INIT VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD'))
);

Prevention Tips

Validate partition boundaries before any DDL: Incorporate a pre-check query into your partition management scripts to fetch the current last partition bound and compare it against the new value before executing ADD PARTITION.
Adopt Interval Partitioning for time-based data: Interval Partitioning eliminates manual partition boundary management and prevents ORA-14074 at the architectural level, making it the recommended approach for date/time-ranged tables in modern Oracle environments.

Related Errors

ORA-14019: Partition bound element is out of the legal range.
ORA-14036: Partition bound value is too large for the column type.
ORA-14400: Inserted partition key does not map to any partition — often a downstream consequence of poor partition boundary design.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community