DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01410 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01410: Invalid ROWID — Causes, Fixes, and Prevention

ORA-01410 occurs in Oracle Database when an operation references a ROWID that is either malformed, no longer valid, or points to a row that no longer exists at that physical location. A ROWID is Oracle's internal pointer to the physical storage location of a row, and it can become invalid after partition moves, table reorganizations, or when an improperly formatted string is cast to a ROWID type. This error is one of those "silent killers" in production systems because it often surfaces long after the root cause was introduced.

Top 3 Causes

1. Invalid String Conversion via CHARTOROWID

Passing an arbitrary or malformed string to CHARTOROWID() is the most common trigger. Oracle ROWIDs follow a strict Base64-encoded format (e.g., AAAE8NAAFAAAACXAAA), and any deviation causes an immediate ORA-01410.

-- BAD: Passing an invalid string as ROWID
SELECT * FROM employees WHERE ROWID = CHARTOROWID('BADVALUE123');
-- Result: ORA-01410: invalid ROWID

-- GOOD: Validate format before conversion using exception handling
DECLARE
  v_rowid ROWID;
BEGIN
  v_rowid := CHARTOROWID('AAAE8NAAFAAAACXAAA');
  SELECT employee_id FROM employees WHERE ROWID = v_rowid INTO :result;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1410 THEN
      DBMS_OUTPUT.PUT_LINE('Caught invalid ROWID — skipping.');
    ELSE
      RAISE;
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Row Movement in Partitioned Tables

When ENABLE ROW MOVEMENT is set on a partitioned table and a partition key column is updated, Oracle physically moves the row to a different partition. Any previously stored or cached ROWID for that row instantly becomes stale and invalid.

-- Check if row movement is enabled
SELECT table_name, row_movement
FROM user_tables
WHERE table_name = 'SALES';

-- DANGEROUS: Storing a ROWID and using it after a potential row move
DECLARE
  v_rowid ROWID;
BEGIN
  SELECT ROWID INTO v_rowid FROM sales WHERE sale_id = 1001;
  -- Partition key updated elsewhere — row has moved!
  UPDATE sales SET amount = 999 WHERE ROWID = v_rowid; -- ORA-01410 risk
END;
/

-- SAFE: Always use the logical primary key instead
BEGIN
  UPDATE sales SET amount = 999 WHERE sale_id = 1001;
  COMMIT;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Referencing a Deleted Row's ROWID

In PL/SQL cursor loops, deleting a row and then attempting to use WHERE CURRENT OF or directly referencing that ROWID later in the same loop causes ORA-01410.

-- PROBLEMATIC pattern
DECLARE
  CURSOR c IS SELECT ROWID, employee_id, salary FROM employees FOR UPDATE;
BEGIN
  FOR r IN c LOOP
    IF r.salary < 2000 THEN
      DELETE FROM employees WHERE ROWID = r.ROWID;
    END IF;
    -- Attempting CURRENT OF after delete on same row = ORA-01410
    UPDATE employees SET salary = salary * 1.05 WHERE CURRENT OF c;
  END LOOP;
END;
/

-- SAFE pattern: Separate delete and update using BULK COLLECT
DECLARE
  TYPE id_list IS TABLE OF NUMBER;
  v_low_ids  id_list;
  v_high_ids id_list;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_low_ids
  FROM employees WHERE salary < 2000;

  SELECT employee_id BULK COLLECT INTO v_high_ids
  FROM employees WHERE salary >= 2000;

  FORALL i IN 1..v_low_ids.COUNT
    DELETE FROM employees WHERE employee_id = v_low_ids(i);

  FORALL i IN 1..v_high_ids.COUNT
    UPDATE employees SET salary = salary * 1.05
    WHERE employee_id = v_high_ids(i);

  COMMIT;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Wrap CHARTOROWID calls in exception handlers that trap SQLCODE = -1410.
  • Replace ROWID-based lookups with primary key or unique index column lookups in all long-lived code paths.
  • Audit stored ROWIDs in application tables or session variables — if found, redesign to use logical keys. 
-- Verify a ROWID is still valid before using it
SELECT COUNT(*) FROM employees WHERE ROWID = :stored_rowid;
-- If result = 0, the ROWID is no longer valid
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Never persist ROWIDs across transactions or sessions. ROWID is a physical address that changes after ALTER TABLE MOVE, SHRINK SPACE, partition operations, or any export/import cycle. Treat it as read-only and session-scoped. Always design your data access layer around logical primary keys.

  2. Audit partitioned tables for ROW MOVEMENT before writing DML code. Query USER_TABLES.ROW_MOVEMENT at development time and enforce a coding standard that prohibits ROWID storage for any table where Row Movement is ENABLED. Include this check in your code review checklist.

Related Errors

Error Code Description
ORA-08006 Row no longer exists — often appears alongside ORA-01410 in concurrent DELETE scenarios
ORA-14402 Updating partition key without ROW MOVEMENT enabled — the flip side of the partition ROWID issue
ORA-01445 Cannot select ROWID from a join view — surfaces when ROWID-based logic is applied to views

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