DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02149 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02149: Specified Partition Does Not Exist — Causes, Fixes & Prevention

ORA-02149 is thrown by Oracle when a SQL statement or DDL command references a partition name that does not exist in the target table or index. This typically occurs during partition maintenance operations such as DROP PARTITION, TRUNCATE PARTITION, or when querying data with an explicit PARTITION clause. If left unhandled, this error can silently break automated maintenance jobs, ETL pipelines, and batch processes that rely on partition-level operations.

Top 3 Causes

1. Typo or Case Mismatch in Partition Name

Oracle stores partition names in uppercase by default in the data dictionary. Passing a lowercase or mixed-case name will cause the lookup to fail and trigger ORA-02149.

-- Check actual partition names (always uppercase in dictionary)
SELECT partition_name, partition_position, high_value
FROM   user_tab_partitions
WHERE  table_name = 'SALES'
ORDER  BY partition_position;

-- WRONG: lowercase name causes ORA-02149
SELECT * FROM sales PARTITION (sales_q1_2024);

-- CORRECT: use the exact name from the dictionary
SELECT * FROM sales PARTITION (SALES_Q1_2024);
Enter fullscreen mode Exit fullscreen mode

2. Partition Was Already Dropped or Restructured

In environments where old partitions are purged regularly (e.g., rolling window partitioning), a partition might be dropped between the time a job was scheduled and the time it runs. MERGE or SPLIT operations also rename or remove partitions, invalidating any hardcoded references.

-- Safely check if a partition exists before acting on it
DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_cnt
  FROM   user_tab_partitions
  WHERE  table_name      = 'SALES'
    AND  partition_name  = 'SALES_Q1_2024';

  IF v_cnt > 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE SALES DROP PARTITION SALES_Q1_2024';
    DBMS_OUTPUT.PUT_LINE('Partition dropped successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Partition not found — skipping.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Applying Partition Syntax to a Non-Partitioned Table

If a table is migrated from partitioned to non-partitioned (or vice versa) and existing scripts are not updated accordingly, Oracle will raise ORA-02149 (or ORA-14501) when a PARTITION clause is used against a regular heap table.

-- Verify whether a table is partitioned before using PARTITION clause
SELECT table_name, partitioned
FROM   user_tables
WHERE  table_name = 'SALES';
-- PARTITIONED = 'YES' means safe to use PARTITION clause
-- PARTITIONED = 'NO'  means remove the PARTITION clause from your query
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: List all partitions for the target table
SELECT partition_name, high_value, num_rows
FROM   user_tab_partitions
WHERE  table_name = 'SALES'
ORDER  BY partition_position;

-- Step 2: Use the correct partition name in your statement
ALTER TABLE sales TRUNCATE PARTITION SALES_Q1_2024;
ALTER TABLE sales DROP    PARTITION SALES_Q1_2024;

-- Step 3: For index partitions, verify with this query
SELECT index_name, partition_name, status
FROM   user_ind_partitions
WHERE  index_name = 'IDX_SALES_DATE';

-- Step 4: Rebuild a specific index partition
ALTER INDEX idx_sales_date REBUILD PARTITION SALES_Q1_2024;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always validate partition existence before DDL

Build a reusable guard into every maintenance script. Never assume a partition exists — always confirm from USER_TAB_PARTITIONS or DBA_TAB_PARTITIONS before executing DROP, TRUNCATE, or REBUILD operations.

-- Reusable existence check snippet
SELECT COUNT(*) INTO v_cnt
FROM   user_tab_partitions
WHERE  table_name     = UPPER(p_table)
  AND  partition_name = UPPER(p_partition);
Enter fullscreen mode Exit fullscreen mode

2. Standardize partition naming conventions

Adopt a consistent naming pattern such as TABLENAME_YYYYMM and document it. When partition names are predictable, dynamic scripts can derive them reliably without hardcoding. Always log any structural partition changes (MERGE, SPLIT, DROP) in your change management system so dependent jobs and queries can be updated promptly.

Related Oracle Errors

Error Code Description
ORA-14501 Object is not partitioned
ORA-14758 Last partition in a Range section cannot be dropped
ORA-02148 Specified partition name is duplicate
ORA-14300 Partitioning key maps to a partition outside maximum permitted number

📖 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.

Top comments (0)