umzzil nng

Posted on Sep 22 Originally published at oraerror.com

Oracle ORA-14090 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14090: Cannot Add Partition When Table Has a DEFAULT Partition

ORA-14090 occurs in Oracle when you attempt to add a new partition to a LIST-partitioned table that already contains a DEFAULT partition. Since the DEFAULT partition is designed to catch all values not explicitly mapped to another partition, Oracle blocks the ADD PARTITION operation to prevent logical data overlap and integrity issues.

Top 3 Causes

1. Adding a LIST Partition to a Table with an Existing DEFAULT Partition

This is the most common cause. Once a DEFAULT partition exists, Oracle cannot allow a new partition to be added directly because the new partition's values already logically belong to the DEFAULT partition.

-- Table with DEFAULT partition
CREATE TABLE sales_list (
    sale_id  NUMBER,
    region   VARCHAR2(20),
    amount   NUMBER
)
PARTITION BY LIST (region) (
    PARTITION p_seoul   VALUES ('SEOUL'),
    PARTITION p_busan   VALUES ('BUSAN'),
    PARTITION p_default VALUES (DEFAULT)
);

-- This will throw ORA-14090
ALTER TABLE sales_list ADD PARTITION p_daegu VALUES ('DAEGU');
-- ORA-14090: cannot add partition when table has a DEFAULT partition

2. Lack of Partition Structure Review Before DDL Execution

Many DBAs run ALTER TABLE ADD PARTITION without first checking whether a DEFAULT partition exists. This is especially common in legacy systems where the original partition design is not well documented.

-- Always check partition structure before any DDL
SELECT partition_name,
       high_value,
       partition_position,
       num_rows
FROM   user_tab_partitions
WHERE  table_name = 'SALES_LIST'
ORDER BY partition_position;

3. Attempting to Expand Partitions Without Using SPLIT PARTITION

When a DEFAULT partition already holds data for values you want to isolate into a new partition, you cannot simply ADD the partition. You must use SPLIT PARTITION to properly separate the data.

-- Incorrect approach (causes ORA-14090)
ALTER TABLE sales_list ADD PARTITION p_daegu VALUES ('DAEGU');

-- Correct approach: check data in DEFAULT first
SELECT COUNT(*) FROM sales_list PARTITION (p_default);

Quick Fix Solutions

Fix 1: Use SPLIT PARTITION (Recommended)

This is the safest approach, especially when the DEFAULT partition already contains data.

-- Split 'DAEGU' out of DEFAULT partition
ALTER TABLE sales_list
    SPLIT PARTITION p_default VALUES ('DAEGU')
    INTO (
        PARTITION p_daegu,
        PARTITION p_default
    );

-- Verify result
SELECT partition_name, high_value
FROM   user_tab_partitions
WHERE  table_name = 'SALES_LIST'
ORDER BY partition_position;

Fix 2: Drop and Recreate DEFAULT Partition (Only If Empty)

If the DEFAULT partition contains no data, you can drop it, add the new partition, then re-add the DEFAULT partition.

-- Step 1: Confirm DEFAULT partition is empty
SELECT COUNT(*) FROM sales_list PARTITION (p_default);

-- Step 2: Drop the DEFAULT partition
ALTER TABLE sales_list DROP PARTITION p_default;

-- Step 3: Add the new partition
ALTER TABLE sales_list ADD PARTITION p_daegu VALUES ('DAEGU');

-- Step 4: Re-add DEFAULT partition
ALTER TABLE sales_list ADD PARTITION p_default VALUES (DEFAULT);

Prevention Tips

1. Design your partition strategy upfront. Decide whether a DEFAULT partition is needed before creating the table. If you anticipate future partition additions, document that SPLIT PARTITION must be used instead of ADD PARTITION.

2. Always query partition metadata before running DDL. Make it a standard practice to check USER_TAB_PARTITIONS for any DEFAULT partitions before executing partition-related DDL statements in production environments.

-- Quick check for DEFAULT partitions across all tables
SELECT table_name, partition_name, high_value
FROM   user_tab_partitions
WHERE  high_value = 'DEFAULT'
ORDER BY table_name;

Related Errors

ORA-14019 – Invalid partition bound value specified
ORA-14074 – New partition bound must be higher than the last partition bound (RANGE)
ORA-14400 – Inserted row does not map to any partition (no DEFAULT partition defined)

📖 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