Oracle ORA-14032 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14032: partition bound of the highest partition must be MAXVALUE

ORA-14032 is thrown by Oracle when performing partition DDL operations on a range-partitioned table where the highest partition does not have MAXVALUE as its upper bound. This error commonly appears during SPLIT PARTITION, ADD PARTITION, or table creation scenarios where the final partition boundary is defined with a specific value rather than MAXVALUE. Understanding this constraint is essential for anyone managing range-partitioned tables in Oracle.

Top 3 Causes

1. SPLIT PARTITION Without Preserving MAXVALUE

When splitting the last partition (which is MAXVALUE), the resulting split must ensure the final output partition still ends with MAXVALUE. Failing to do so triggers ORA-14032 immediately.

-- WRONG: Second partition has a fixed upper bound, not MAXVALUE
ALTER TABLE sales SPLIT PARTITION p_max
AT (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
INTO (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) -- ERROR
);

-- CORRECT: Last partition must end with MAXVALUE
ALTER TABLE sales SPLIT PARTITION p_max
AT (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
INTO (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_max  VALUES LESS THAN (MAXVALUE) -- Always end with MAXVALUE
);

2. ADD PARTITION When MAXVALUE Already Exists

If the last partition is already defined as MAXVALUE, you cannot use ADD PARTITION to append a new range above it — logically impossible since MAXVALUE covers everything. Use SPLIT PARTITION instead.

-- WRONG: Trying to ADD PARTITION beyond MAXVALUE
ALTER TABLE sales ADD PARTITION p_2025
    VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')); -- ORA-14032 or ORA-14074

-- CORRECT: Split the existing MAXVALUE partition
ALTER TABLE sales SPLIT PARTITION p_max
AT (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
INTO (
    PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
    PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

3. Table Created Without a MAXVALUE Partition

A range-partitioned table created without a final MAXVALUE partition can cause ORA-14032 during subsequent DDL operations such as MERGE PARTITION or EXCHANGE PARTITION.

-- WRONG: No MAXVALUE partition defined
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE,
    amount     NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    -- Missing MAXVALUE partition!
);

-- CORRECT: Always include a MAXVALUE catch-all partition
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE,
    amount     NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

-- Fix an existing table missing MAXVALUE
ALTER TABLE orders ADD PARTITION p_max VALUES LESS THAN (MAXVALUE);

Quick Fix Solutions

Step 1 — Diagnose the current partition structure:

SELECT partition_name, partition_position, high_value
FROM user_tab_partitions
WHERE table_name = 'YOUR_TABLE_NAME'
ORDER BY partition_position;

Step 2 — Apply the appropriate fix based on your scenario:

If the last partition has no MAXVALUE → Use ALTER TABLE ... ADD PARTITION p_max VALUES LESS THAN (MAXVALUE)
If splitting a MAXVALUE partition → Ensure the second output partition uses VALUES LESS THAN (MAXVALUE)
Consider switching to Interval Partitioning to avoid manual management entirely:

-- Interval Partitioning: Oracle auto-creates partitions as data arrives
CREATE TABLE sales_auto (
    sale_id   NUMBER,
    sale_date DATE,
    amount    NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

Prevention Tips

Always design range partition tables with a MAXVALUE partition. Make it a team coding standard and include it in your DDL review checklist. Every range-partitioned table should have a catch-all MAXVALUE partition as the last entry — no exceptions.
Prefer Interval Partitioning for date/number-based partitioning. Interval partitioning automatically creates new partitions as data arrives, eliminating the need to manually manage partition boundaries and completely avoiding ORA-14032 in day-to-day operations.

Related Errors

ORA-14074 — New partition bound does not collate higher than the last partition's bound.
ORA-14400 — Inserted partition key does not map to any partition (caused by missing MAXVALUE).
ORA-14019 — Invalid partition bound element type specified.

📖 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