Oracle ORA-14427 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14427: table does not have ROW MOVEMENT enabled

ORA-14427 is an Oracle error that occurs when you attempt an operation requiring row relocation on a table that has not had ROW MOVEMENT enabled. This most commonly happens with partitioned tables when updating a partition key column, or when using the Flashback Table feature. Since Oracle disables ROW MOVEMENT by default, you must explicitly enable it before performing such operations.

Top 3 Causes

1. Updating a Partition Key Column on a Partitioned Table

When you update the partition key column of a row in a partitioned table, Oracle needs to physically move that row to a different partition. Without ROW MOVEMENT enabled, Oracle refuses this operation and raises ORA-14427.

-- This will raise ORA-14427 if ROW MOVEMENT is disabled
UPDATE sales
SET sale_date = DATE '2024-03-01'
WHERE sale_id = 5001;

-- Fix: Enable ROW MOVEMENT first
ALTER TABLE sales ENABLE ROW MOVEMENT;

-- Now the UPDATE will succeed
UPDATE sales
SET sale_date = DATE '2024-03-01'
WHERE sale_id = 5001;

COMMIT;

2. Using Flashback Table Without ROW MOVEMENT Enabled

Oracle's Flashback Table feature restores a table to a previous point in time. Because this process can change existing ROWIDs, Oracle requires ROW MOVEMENT to be enabled before executing a FLASHBACK TABLE statement.

-- Step 1: Enable ROW MOVEMENT
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- Step 2: Flashback the table to a specific timestamp
FLASHBACK TABLE employees TO TIMESTAMP
    TO_TIMESTAMP('2024-05-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Step 3: Optionally disable after the operation
ALTER TABLE employees DISABLE ROW MOVEMENT;

3. Partition Maintenance and Data Migration Scripts

Bulk data migration or partition reorganization scripts often update partition key values en masse. If ROW MOVEMENT is not enabled beforehand, even a single row requiring a partition change will cause the entire batch to fail with ORA-14427.

-- Check ROW MOVEMENT status before running migration
SELECT table_name, partitioned, row_movement
FROM dba_tables
WHERE owner = 'APPUSER'
  AND partitioned = 'YES'
  AND row_movement = 'DISABLED';

-- Bulk enable ROW MOVEMENT for all partitioned tables in a schema
BEGIN
    FOR rec IN (
        SELECT table_name
        FROM dba_tables
        WHERE owner = 'APPUSER'
          AND partitioned = 'YES'
          AND row_movement = 'DISABLED'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TABLE APPUSER.' || rec.table_name || ' ENABLE ROW MOVEMENT';
    END LOOP;
END;
/

Quick Fix

The immediate fix is straightforward — enable ROW MOVEMENT on the affected table:

-- Enable ROW MOVEMENT
ALTER TABLE <table_name> ENABLE ROW MOVEMENT;

-- Verify the change
SELECT table_name, row_movement
FROM dba_tables
WHERE table_name = '<TABLE_NAME>';

If you need to disable it again after completing your operation:

ALTER TABLE <table_name> DISABLE ROW MOVEMENT;

Prevention Tips

Enable ROW MOVEMENT at table creation time for all partitioned tables where partition key updates are possible. Make this a standard part of your DDL templates.

CREATE TABLE orders (
    order_id    NUMBER,
    order_date  DATE,
    customer_id NUMBER,
    total       NUMBER(15,2)
)
ENABLE ROW MOVEMENT
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_future VALUES LESS THAN (MAXVALUE)
);

Schedule a regular audit query to identify partitioned tables with ROW MOVEMENT disabled. Incorporate this into your weekly DBA health-check reports to catch issues before they surface in production.

-- Weekly audit: partitioned tables missing ROW MOVEMENT
SELECT owner, table_name, num_rows, row_movement
FROM dba_tables
WHERE partitioned = 'YES'
  AND row_movement = 'DISABLED'
  AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
ORDER BY owner, table_name;

Related Errors

ORA-14402 — Raised when updating a partition key column would cause a partition change and ROW MOVEMENT is disabled.
ORA-08189 — Specifically raised during Flashback Table when ROW MOVEMENT is not enabled.
ORA-14400 — Occurs when an inserted or updated partition key value does not map to any existing partition.

📖 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