umzzil nng

Posted on Sep 19 Originally published at oraerror.com

Oracle ORA-14016 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14016: underlying table of a local index must be partitioned

ORA-14016 is thrown by Oracle when you attempt to create a local index on a table that is not partitioned. A local index is tightly coupled with the partition structure of its underlying table — one index partition per table partition — so it fundamentally cannot exist on a non-partitioned (heap) table. If you encounter this error, the fix is either to drop the LOCAL keyword or to convert the table into a partitioned table first.

Top 3 Causes

1. Creating a LOCAL Index on a Regular (Non-Partitioned) Table

The most common cause: a developer copies a DDL script from a partitioned environment and runs it against a non-partitioned table without checking the table structure first.

-- Create a plain, non-partitioned table
CREATE TABLE orders_normal (
    order_id   NUMBER,
    order_date DATE,
    amount     NUMBER
);

-- This will trigger ORA-14016
CREATE INDEX idx_orders_local ON orders_normal(order_date) LOCAL;
-- ORA-14016: underlying table of a local index must be partitioned

-- Verify partition status before creating indexes
SELECT table_name, partitioned
FROM   user_tables
WHERE  table_name = 'ORDERS_NORMAL';
-- PARTITIONED = 'NO' confirms the problem

2. Environment Mismatch Between Production and Dev/Test

A table may exist as a partitioned object in production but was created as a plain table in a lower environment. Running the same index DDL across environments causes ORA-14016 in non-partitioned environments.

-- Check partition status across schemas or environments
SELECT owner, table_name, partitioned
FROM   dba_tables
WHERE  table_name = 'ORDERS_NORMAL'
ORDER BY owner;

-- List existing partitions (empty result = not partitioned)
SELECT partition_name, partition_position
FROM   user_tab_partitions
WHERE  table_name = 'ORDERS_NORMAL';

3. Incorrect Sequence During Table Redefinition or Migration

When converting a partitioned table to a non-partitioned one (or vice versa) using tools like DBMS_REDEFINITION or Data Pump, index creation scripts may run before the table's partition structure is properly established.

-- Always verify table type BEFORE running index DDL
SELECT t.table_name,
       t.partitioned,
       COUNT(p.partition_name) AS partition_count
FROM   user_tables t
LEFT JOIN user_tab_partitions p ON p.table_name = t.table_name
WHERE  t.table_name = 'ORDERS_NORMAL'
GROUP BY t.table_name, t.partitioned;

Quick Fix Solutions

Fix 1: Remove the LOCAL Keyword (Use a Global Index Instead)

If you don't need partition-level index management, simply drop LOCAL and create a standard global index.

-- Works on any table, partitioned or not
CREATE INDEX idx_orders_date ON orders_normal(order_date);

-- Confirm index creation
SELECT index_name, partitioned, status
FROM   user_indexes
WHERE  table_name = 'ORDERS_NORMAL';

Fix 2: Convert the Table to a Partitioned Table, Then Create the LOCAL Index

If a local index is a hard requirement (e.g., for partition pruning or manageability), recreate the table as a partitioned table first.

-- Step 1: Create partitioned version of the table
CREATE TABLE orders_part (
    order_id   NUMBER,
    order_date DATE,
    amount     NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

-- Step 2: Migrate data
INSERT INTO orders_part SELECT * FROM orders_normal;
COMMIT;

-- Step 3: Create the LOCAL index (succeeds now)
CREATE INDEX idx_orders_part_local ON orders_part(order_date) LOCAL;

-- Step 4: Verify local index partitions
SELECT index_name, partition_name, status
FROM   user_ind_partitions
WHERE  index_name = 'IDX_ORDERS_PART_LOCAL';

Prevention Tips

1. Validate partition status before any index DDL

Add a pre-check step to your deployment scripts or CI/CD pipeline to catch this issue before it reaches production.

-- Quick validation query — run before creating a LOCAL index
SELECT CASE
           WHEN partitioned = 'YES' THEN 'OK: Safe to create LOCAL index'
           ELSE 'ERROR: Table is not partitioned. LOCAL index will fail.'
       END AS check_result
FROM   user_tables
WHERE  table_name = UPPER('&table_name');

2. Standardize DDL templates across environments

Maintain a single source of truth for table DDLs — including partition definitions — and enforce code reviews that require checking the LOCAL keyword against the target table's partition status. Clearly comment partition intent in every CREATE TABLE statement to prevent environment-specific drift.

Related Oracle Errors

ORA-14017 — Partition bound is invalid for a local index; check partition boundary definitions.
ORA-14006 — Invalid partition name referenced during index or partition operations.
ORA-14019 — Partition bound element is out of the legal range; often seen alongside partitioning setup issues.
ORA-02149 — Specified partition does not exist; can surface when querying local index partitions that haven't been created yet.

📖 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