Oracle ORA-14424 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-14424: Cannot Shrink a Segment That Does Not Have Row Movement Enabled

ORA-14424 occurs in Oracle Database when you attempt to shrink a table or segment using ALTER TABLE ... SHRINK SPACE without first enabling the ROW MOVEMENT feature on that object. Oracle's shrink operation physically relocates rows within the segment to reclaim fragmented free space, which requires row movement to be explicitly enabled. This error is commonly encountered during space reclamation tasks after large-scale data deletions.

Top 3 Causes

1. ROW MOVEMENT Not Enabled on the Target Table

By default, Oracle creates tables with ROW MOVEMENT disabled. Any attempt to run SHRINK SPACE without enabling it will immediately raise ORA-14424.

-- Check current ROW MOVEMENT status
SELECT table_name, row_movement
FROM dba_tables
WHERE table_name = 'YOUR_TABLE'
  AND owner = 'YOUR_SCHEMA';

-- This will fail if ROW MOVEMENT is DISABLED
ALTER TABLE your_schema.your_table SHRINK SPACE;
-- ORA-14424: cannot shrink a segment that does not have row movement enabled

2. Partitioned Table SHRINK Without Table-Level ROW MOVEMENT

When attempting to shrink a specific partition of a partitioned table, ROW MOVEMENT must be enabled at the table level, not the partition level. Overlooking this distinction is a common mistake.

-- Incorrect assumption: trying to shrink a partition directly
ALTER TABLE your_schema.sales_part
  MODIFY PARTITION sales_2023 SHRINK SPACE;
-- Fails if ROW MOVEMENT is not enabled on the table

-- Correct approach: enable at table level first
ALTER TABLE your_schema.sales_part ENABLE ROW MOVEMENT;

ALTER TABLE your_schema.sales_part
  MODIFY PARTITION sales_2023 SHRINK SPACE;

3. LOB Segment SHRINK Without Enabling ROW MOVEMENT

Tables containing LOB columns require ROW MOVEMENT to be enabled on the parent table before shrinking the LOB segment. Skipping this step results in ORA-14424.

-- Check LOB segments in a table
SELECT column_name, segment_name, tablespace_name
FROM dba_lobs
WHERE table_name = 'YOUR_TABLE'
  AND owner = 'YOUR_SCHEMA';

-- Enable ROW MOVEMENT before LOB shrink
ALTER TABLE your_schema.your_table ENABLE ROW MOVEMENT;

-- Shrink the LOB segment
ALTER TABLE your_schema.your_table
  MODIFY LOB (your_lob_column) (SHRINK SPACE);

Quick Fix Solutions

The fix is straightforward: enable ROW MOVEMENT before running SHRINK, then optionally disable it afterward.

-- Step 1: Enable ROW MOVEMENT
ALTER TABLE your_schema.your_table ENABLE ROW MOVEMENT;

-- Step 2: Shrink the segment (reclaim fragmented space)
ALTER TABLE your_schema.your_table SHRINK SPACE;

-- Step 3: Shrink including indexes (CASCADE)
ALTER TABLE your_schema.your_table SHRINK SPACE CASCADE;

-- Step 4: (Optional) Disable ROW MOVEMENT after completion
ALTER TABLE your_schema.your_table DISABLE ROW MOVEMENT;

For bulk operations across multiple tables:

BEGIN
  FOR rec IN (
    SELECT owner, table_name
    FROM dba_tables
    WHERE owner = 'YOUR_SCHEMA'
      AND row_movement = 'DISABLED'
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.owner || '.' || rec.table_name
                        || ' ENABLE ROW MOVEMENT';
      EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.owner || '.' || rec.table_name
                        || ' SHRINK SPACE CASCADE';
      DBMS_OUTPUT.PUT_LINE('Processed: ' || rec.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed: ' || rec.table_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

Prevention Tips

Enable ROW MOVEMENT at table creation for tables that undergo frequent large deletes, so space reclamation scripts never fail unexpectedly.

CREATE TABLE your_schema.your_table (
  id   NUMBER PRIMARY KEY,
  name VARCHAR2(100)
)
ENABLE ROW MOVEMENT;

Add a pre-check in your maintenance scripts to automatically verify and enable ROW MOVEMENT before any SHRINK operation, and log before/after segment sizes to validate space savings.

Related Errors

ORA-10636 – Internal row movement failure during shrink operations.
ORA-14412 – Shrink not supported for certain partition or subpartition types.
ORA-01654 – Tablespace out of space; verify free space before running SHRINK.

📖 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