ORA-14400 Inserted Partition Key Does Not Map to Any Partition - Resolution Guide

ORA-14400: Inserted Partition Key Does Not Map to Any Partition

Error Overview

Error Text: ORA-14400: inserted partition key does not map to any partition

This error occurs when you try to INSERT or UPDATE a row whose partition key value doesn’t fall within any defined partition range. It’s most common with RANGE-partitioned tables where new data exceeds the highest partition boundary, and with LIST-partitioned tables where a value isn’t in any partition’s value list.

Common Causes

1. Range Partition Boundary Exceeded

• Date-based partitions haven’t been created for the current period
• Partition maintenance job failed to create future partitions
• Data arriving for unexpected future dates

2. List Partition Value Missing

• New category or status value not in any partition’s list
• Application added a new enum value without updating partitions

3. NULL Partition Key

• NULL value in partition key column
• No DEFAULT partition defined for LIST partitions

4. Subpartition Key Mismatch

• Composite partitioning where subpartition key doesn’t map

5. Data Migration Issues

• Source data has values outside expected ranges
• Time zone differences causing date values to fall outside partitions

Diagnostic Queries

Check Existing Partition Boundaries

-- Range partitions: see all boundary values
SELECT partition_name, high_value, num_rows, last_analyzed
FROM dba_tab_partitions
WHERE table_owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
ORDER BY partition_position;

-- List partitions: see defined values
SELECT partition_name, high_value, num_rows
FROM dba_tab_partitions
WHERE table_owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
ORDER BY partition_position;

-- Check partition type
SELECT partitioning_type, subpartitioning_type, partition_count
FROM dba_part_tables
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE';

Find the Offending Data

-- For date-based range partitions, find data beyond last partition
-- First, get the highest partition boundary
SELECT MAX(partition_position), partition_name, high_value
FROM dba_tab_partitions
WHERE table_owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
GROUP BY partition_name, high_value
ORDER BY MAX(partition_position) DESC
FETCH FIRST 1 ROW ONLY;

-- Then check for data that would fall outside
-- (Example for date partition key)
SELECT MIN(date_column), MAX(date_column), COUNT(*)
FROM staging_table
WHERE date_column >= TO_DATE('2026-01-01', 'YYYY-MM-DD');  -- Adjust to your last partition boundary

-- For list partitions, find unpartitioned values
SELECT DISTINCT status_column, COUNT(*)
FROM staging_table
GROUP BY status_column
ORDER BY status_column;

Check for Interval Partitioning

-- Interval partitions auto-create; if this error occurs on interval table, something is wrong
SELECT table_name, partitioning_type, interval
FROM dba_part_tables
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE';

-- If interval is NULL, it's standard range partitioning (no auto-create)

Resolution Steps

Solution 1: Add Missing Range Partitions

-- Add partitions for upcoming periods
ALTER TABLE sales ADD PARTITION p_2026_q2
  VALUES LESS THAN (TO_DATE('2026-07-01', 'YYYY-MM-DD'));

ALTER TABLE sales ADD PARTITION p_2026_q3
  VALUES LESS THAN (TO_DATE('2026-10-01', 'YYYY-MM-DD'));

ALTER TABLE sales ADD PARTITION p_2026_q4
  VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'));

-- Add a MAXVALUE partition as a catch-all
ALTER TABLE sales ADD PARTITION p_future
  VALUES LESS THAN (MAXVALUE);

Solution 2: Convert to Interval Partitioning (11g+)

-- Convert existing range-partitioned table to interval
-- Requires at least one existing partition
ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

-- For numeric range partitions
ALTER TABLE transactions SET INTERVAL (1000000);

-- Verify interval is set
SELECT table_name, interval
FROM dba_part_tables
WHERE table_name = 'SALES';

-- New partitions are now created automatically as data arrives

Solution 3: Add Missing List Partition Values

-- Add a new value to an existing list partition
ALTER TABLE orders MODIFY PARTITION p_active
  ADD VALUES ('PENDING_REVIEW');

-- Or create a new partition for the value
ALTER TABLE orders ADD PARTITION p_new_status
  VALUES ('PENDING_REVIEW', 'ON_HOLD');

-- Add a DEFAULT partition to catch any unmapped values
ALTER TABLE orders ADD PARTITION p_default VALUES (DEFAULT);

Solution 4: Split an Existing Partition

-- Split a MAXVALUE or DEFAULT partition to add specific ranges
ALTER TABLE sales SPLIT PARTITION p_future AT (TO_DATE('2027-01-01', 'YYYY-MM-DD'))
  INTO (PARTITION p_2026_h2, PARTITION p_future);

-- Split a list DEFAULT partition
ALTER TABLE orders SPLIT PARTITION p_default
  VALUES ('NEW_STATUS')
  INTO (PARTITION p_new_status, PARTITION p_default);

Solution 5: Handle NULL Partition Keys

-- For list partitions, add NULL handling
ALTER TABLE orders ADD PARTITION p_null VALUES (NULL);

-- Or add to DEFAULT partition
ALTER TABLE orders ADD PARTITION p_default VALUES (DEFAULT);

-- For range partitions, NULLs go to the highest partition
-- or need a MAXVALUE partition

Automated Partition Management

Auto-Create Partitions Job

-- Create a job to pre-create monthly partitions
CREATE OR REPLACE PROCEDURE create_monthly_partitions(
  p_table_name VARCHAR2,
  p_months_ahead NUMBER DEFAULT 3
) AS
  v_sql VARCHAR2(4000);
  v_date DATE;
  v_part_name VARCHAR2(30);
  v_exists NUMBER;
BEGIN
  FOR i IN 0..p_months_ahead LOOP
    v_date := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), i + 1);
    v_part_name := 'P_' || TO_CHAR(v_date - 1, 'YYYY_MM');

    -- Check if partition already exists
    SELECT COUNT(*) INTO v_exists
    FROM user_tab_partitions
    WHERE table_name = UPPER(p_table_name)
      AND partition_name = v_part_name;

    IF v_exists = 0 THEN
      v_sql := 'ALTER TABLE ' || p_table_name ||
               ' ADD PARTITION ' || v_part_name ||
               ' VALUES LESS THAN (TO_DATE(''' ||
               TO_CHAR(v_date, 'YYYY-MM-DD') ||
               ''', ''YYYY-MM-DD''))';
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE('Created partition: ' || v_part_name);
    END IF;
  END LOOP;
END;
/

-- Schedule to run weekly
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'CREATE_PARTITIONS_JOB',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN create_monthly_partitions(''SALES'', 3); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=2',
    enabled    => TRUE
  );
END;
/

Monitoring for Partition Gaps

-- Alert when highest partition is within 30 days of being full
SELECT table_name, partition_name, high_value
FROM user_tab_partitions p
WHERE table_name = 'SALES'
  AND partition_position = (
    SELECT MAX(partition_position)
    FROM user_tab_partitions
    WHERE table_name = p.table_name
  );
-- Manually evaluate high_value to check if it's < SYSDATE + 30

Prevention Strategies

1. Use Interval Partitioning

-- Best approach for date-based range partitions
CREATE TABLE sales (
  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('2024-01-01', 'YYYY-MM-DD'))
);
-- New monthly partitions created automatically

2. Always Include DEFAULT/MAXVALUE

-- Range: add MAXVALUE partition
PARTITION p_max VALUES LESS THAN (MAXVALUE)

-- List: add DEFAULT partition
PARTITION p_default VALUES (DEFAULT)

3. Validate Data Before Loading

-- Check for out-of-range values before bulk insert
SELECT COUNT(*) as out_of_range
FROM staging_table
WHERE date_column >= (
  -- Get highest non-MAXVALUE boundary
  SELECT MAX(TO_DATE(high_value)) FROM user_tab_partitions
  WHERE table_name = 'TARGET_TABLE'
    AND high_value != 'MAXVALUE'
);

Related Errors

• ORA-01688: Unable to Extend Table Partition - Partition tablespace full
• ORA-01653: Unable to Extend Table - Table tablespace full
• ORA-00942: Table or View Does Not Exist - Table access issues
• ORA-01400: Cannot Insert NULL - NULL in partition key