ORA-01410 Invalid ROWID - Fix Oracle ROWID Errors

ORA-01410: Invalid ROWID

Error Overview

Error Text: ORA-01410: invalid ROWID

This error occurs when Oracle attempts to access a row using a ROWID that is no longer valid, doesn’t exist, or has been corrupted. ROWIDs are physical addresses of rows in the database, and they can become invalid after operations like table reorganization, export/import, or data corruption.

Understanding ROWIDs

ROWID Structure

Oracle ROWID Components (Extended Format)
├── Object Number (Data Object ID)
├── Relative File Number
├── Block Number
└── Row Number (Slot within block)

Format: OOOOOOFFFBBBBBBRRR
O = Object, F = File, B = Block, R = Row

Common Scenarios

• Table reorganization - ROWID changes after table move or rebuild
• Export/Import operations - ROWIDs don’t persist across export/import
• Deleted rows - Accessing ROWID of deleted data
• Corrupted ROWIDs - Data corruption or invalid ROWID strings
• Partition operations - ROWIDs change during partition maintenance

Diagnostic Steps

1. Validate ROWID Format and Existence

-- Check if ROWID is valid format
DECLARE
  v_rowid ROWID;
  v_valid BOOLEAN := TRUE;
BEGIN
  BEGIN
    v_rowid := CHARTOROWID('AAAR3sAAEAAAACXAAA');  -- Replace with your ROWID
    DBMS_OUTPUT.PUT_LINE('ROWID format is valid');
  EXCEPTION
    WHEN OTHERS THEN
      v_valid := FALSE;
      DBMS_OUTPUT.PUT_LINE('Invalid ROWID format: ' || SQLERRM);
  END;

  -- If format is valid, try to access the row
  IF v_valid THEN
    DECLARE
      v_count NUMBER;
    BEGIN
      EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM your_table WHERE ROWID = :1'
      INTO v_count
      USING v_rowid;

      IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ROWID does not exist in table');
      ELSE
        DBMS_OUTPUT.PUT_LINE('ROWID exists and is accessible');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error accessing ROWID: ' || SQLERRM);
    END;
  END IF;
END;
/

-- Decode ROWID components
SELECT
  ROWID,
  DBMS_ROWID.ROWID_OBJECT(ROWID) as object_id,
  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) as file_number,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) as block_number,
  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) as row_number
FROM your_table
WHERE ROWNUM <= 10;  -- Sample rows

-- Verify object exists for ROWID
SELECT
  object_name,
  object_type,
  status
FROM dba_objects
WHERE object_id = DBMS_ROWID.ROWID_OBJECT(CHARTOROWID('your_rowid_here'));

2. Check for Table Modifications

-- Check recent DDL operations on table
SELECT
  object_name,
  object_type,
  last_ddl_time,
  created,
  status
FROM dba_objects
WHERE object_name = 'YOUR_TABLE'
  AND owner = 'YOUR_SCHEMA'
ORDER BY last_ddl_time DESC;

-- Check for table moves or rebuilds
SELECT
  owner,
  segment_name,
  segment_type,
  tablespace_name,
  bytes/1024/1024 as size_mb,
  last_analyzed
FROM dba_segments
WHERE segment_name = 'YOUR_TABLE'
  AND owner = 'YOUR_SCHEMA';

-- Check partition operations history
SELECT
  table_owner,
  table_name,
  partition_name,
  high_value,
  last_analyzed,
  num_rows
FROM dba_tab_partitions
WHERE table_name = 'YOUR_TABLE'
  AND table_owner = 'YOUR_SCHEMA'
ORDER BY partition_position;

-- Check for recent analyze operations
SELECT
  owner,
  table_name,
  last_analyzed,
  num_rows,
  blocks,
  avg_row_len,
  stale_stats
FROM dba_tab_statistics
WHERE table_name = 'YOUR_TABLE'
  AND owner = 'YOUR_SCHEMA';

3. Identify Stale ROWIDs in Application

-- Find stored ROWIDs that may be stale
-- Example: Check a table that stores ROWIDs
SELECT
  stored_rowid_column,
  COUNT(*) as count
FROM application_table
WHERE stored_rowid_column IS NOT NULL
GROUP BY stored_rowid_column
HAVING COUNT(*) > 1;  -- Duplicate ROWIDs indicate issues

-- Validate stored ROWIDs against actual table
CREATE OR REPLACE FUNCTION validate_stored_rowids(
  p_table_name VARCHAR2,
  p_rowid_column VARCHAR2,
  p_target_table VARCHAR2
) RETURN NUMBER AS
  v_sql VARCHAR2(4000);
  v_invalid_count NUMBER := 0;
  v_rowid ROWID;
  v_exists NUMBER;
BEGIN
  -- Build dynamic SQL to check each stored ROWID
  v_sql := 'SELECT ' || p_rowid_column || ' FROM ' || p_table_name ||
           ' WHERE ' || p_rowid_column || ' IS NOT NULL';

  FOR rec IN (EXECUTE IMMEDIATE v_sql) LOOP
    BEGIN
      v_rowid := CHARTOROWID(rec.stored_rowid);

      EXECUTE IMMEDIATE
        'SELECT 1 FROM ' || p_target_table || ' WHERE ROWID = :1'
      INTO v_exists
      USING v_rowid;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_invalid_count := v_invalid_count + 1;
      WHEN OTHERS THEN
        v_invalid_count := v_invalid_count + 1;
    END;
  END LOOP;

  RETURN v_invalid_count;
END;
/

4. Check for Data Corruption

-- Analyze table for corruption
ANALYZE TABLE your_table VALIDATE STRUCTURE CASCADE;

-- Check for corrupt blocks
SELECT
  file_id,
  block_id,
  blocks,
  corruption_type,
  description
FROM v$database_block_corruption
WHERE file_id IN (
  SELECT DISTINCT file_id
  FROM dba_extents
  WHERE segment_name = 'YOUR_TABLE'
);

-- Use DBMS_REPAIR to check for corruption
DECLARE
  v_corrupt_count NUMBER;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'YOUR_SCHEMA',
    object_name => 'YOUR_TABLE',
    corrupt_count => v_corrupt_count
  );

  DBMS_OUTPUT.PUT_LINE('Corrupt blocks found: ' || v_corrupt_count);
END;
/

Immediate Solutions

Solution 1: Fix Invalid ROWID References

Update or Remove Invalid ROWIDs

-- Remove invalid ROWID references
DELETE FROM application_table
WHERE NOT EXISTS (
  SELECT 1
  FROM target_table t
  WHERE t.ROWID = CHARTOROWID(application_table.stored_rowid_column)
);

-- Update with new ROWIDs based on primary key
UPDATE application_table a
SET stored_rowid_column = (
  SELECT ROWIDTOCHAR(ROWID)
  FROM target_table t
  WHERE t.primary_key = a.foreign_key_reference
)
WHERE EXISTS (
  SELECT 1
  FROM target_table t
  WHERE t.primary_key = a.foreign_key_reference
);

-- Create procedure to fix invalid ROWIDs
CREATE OR REPLACE PROCEDURE fix_invalid_rowids(
  p_table_name VARCHAR2,
  p_rowid_column VARCHAR2,
  p_key_column VARCHAR2,
  p_target_table VARCHAR2,
  p_target_key VARCHAR2
) AS
  v_sql VARCHAR2(4000);
  v_fixed_count NUMBER := 0;
BEGIN
  -- Update ROWIDs based on key relationship
  v_sql := 'UPDATE ' || p_table_name || ' t1 ' ||
           'SET ' || p_rowid_column || ' = (' ||
           '  SELECT ROWIDTOCHAR(ROWID) FROM ' || p_target_table || ' t2 ' ||
           '  WHERE t2.' || p_target_key || ' = t1.' || p_key_column || ')' ||
           'WHERE EXISTS (' ||
           '  SELECT 1 FROM ' || p_target_table || ' t2 ' ||
           '  WHERE t2.' || p_target_key || ' = t1.' || p_key_column || ')';

  EXECUTE IMMEDIATE v_sql;
  v_fixed_count := SQL%ROWCOUNT;

  DBMS_OUTPUT.PUT_LINE('Fixed ' || v_fixed_count || ' ROWID references');
  COMMIT;
END;
/

Solution 2: Rebuild ROWID-Based Access

Convert to Primary Key Based Access

-- Replace ROWID-based queries with primary key
-- Instead of:
-- SELECT * FROM employees WHERE ROWID = :rowid_var;

-- Use:
-- SELECT * FROM employees WHERE employee_id = :emp_id;

-- Create mapping table for ROWID to primary key
CREATE TABLE rowid_mapping (
  table_name VARCHAR2(30),
  old_rowid VARCHAR2(18),
  primary_key_value VARCHAR2(100),
  new_rowid VARCHAR2(18),
  mapping_date DATE DEFAULT SYSDATE
);

-- Populate mapping table
INSERT INTO rowid_mapping (table_name, primary_key_value, new_rowid)
SELECT
  'EMPLOYEES',
  employee_id,
  ROWIDTOCHAR(ROWID)
FROM employees;

-- Function to get current ROWID from primary key
CREATE OR REPLACE FUNCTION get_current_rowid(
  p_table_name VARCHAR2,
  p_key_column VARCHAR2,
  p_key_value VARCHAR2
) RETURN VARCHAR2 AS
  v_rowid VARCHAR2(18);
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT ROWIDTOCHAR(ROWID) FROM ' || p_table_name ||
           ' WHERE ' || p_key_column || ' = :1';

  EXECUTE IMMEDIATE v_sql INTO v_rowid USING p_key_value;

  RETURN v_rowid;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE;
END;
/

Solution 3: Handle Table Reorganization

Update ROWIDs After Table Move

-- Before table move, save ROWIDs with primary keys
CREATE TABLE employees_rowid_backup AS
SELECT
  employee_id,
  ROWIDTOCHAR(ROWID) as old_rowid
FROM employees;

-- Move table (this changes ROWIDs)
ALTER TABLE employees MOVE TABLESPACE new_tablespace;

-- Rebuild indexes after move
ALTER INDEX emp_pk REBUILD;
ALTER INDEX emp_name_idx REBUILD;

-- Update applications with new ROWIDs
UPDATE application_references ar
SET rowid_column = (
  SELECT ROWIDTOCHAR(e.ROWID)
  FROM employees e
  WHERE e.employee_id = (
    SELECT employee_id
    FROM employees_rowid_backup b
    WHERE b.old_rowid = ar.rowid_column
  )
)
WHERE EXISTS (
  SELECT 1
  FROM employees_rowid_backup b
  WHERE b.old_rowid = ar.rowid_column
);

-- Clean up
DROP TABLE employees_rowid_backup;

Long-Term Solutions

1. Implement ROWID Management Strategy

Create ROWID Tracking System

-- Create comprehensive ROWID management package
CREATE OR REPLACE PACKAGE rowid_manager AS

  -- Track ROWID changes
  PROCEDURE track_rowid_change(
    p_table_name VARCHAR2,
    p_key_value VARCHAR2,
    p_old_rowid VARCHAR2,
    p_new_rowid VARCHAR2
  );

  -- Validate all stored ROWIDs
  FUNCTION validate_all_rowids RETURN NUMBER;

  -- Refresh ROWIDs after maintenance
  PROCEDURE refresh_rowids_after_reorg(p_table_name VARCHAR2);

  -- Convert to key-based access
  PROCEDURE migrate_from_rowid_access(p_table_name VARCHAR2);

END rowid_manager;
/

CREATE OR REPLACE PACKAGE BODY rowid_manager AS

  PROCEDURE track_rowid_change(
    p_table_name VARCHAR2,
    p_key_value VARCHAR2,
    p_old_rowid VARCHAR2,
    p_new_rowid VARCHAR2
  ) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO rowid_change_log (
      table_name, key_value, old_rowid, new_rowid, change_date
    ) VALUES (
      p_table_name, p_key_value, p_old_rowid, p_new_rowid, SYSDATE
    );
    COMMIT;
  END track_rowid_change;

  FUNCTION validate_all_rowids RETURN NUMBER AS
    v_invalid_count NUMBER := 0;
    v_table_count NUMBER := 0;
  BEGIN
    -- Check all tables with ROWID references
    FOR tab IN (
      SELECT DISTINCT table_name, rowid_column
      FROM rowid_reference_registry
      WHERE is_active = 'Y'
    ) LOOP
      v_table_count := v_table_count + 1;

      -- Count invalid ROWIDs in each table
      EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' || tab.table_name ||
        ' WHERE ' || tab.rowid_column || ' IS NOT NULL' ||
        ' AND NOT EXISTS (SELECT 1 FROM ' || tab.table_name ||
        ' WHERE ROWID = CHARTOROWID(' || tab.rowid_column || '))'
      INTO v_invalid_count;

      IF v_invalid_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Table ' || tab.table_name ||
                           ' has ' || v_invalid_count || ' invalid ROWIDs');
      END IF;
    END LOOP;

    RETURN v_invalid_count;
  END validate_all_rowids;

  PROCEDURE refresh_rowids_after_reorg(p_table_name VARCHAR2) AS
    v_refresh_count NUMBER := 0;
  BEGIN
    -- Update all ROWID references for reorganized table
    FOR ref IN (
      SELECT referencing_table, rowid_column, key_column
      FROM rowid_reference_registry
      WHERE referenced_table = p_table_name
        AND is_active = 'Y'
    ) LOOP
      EXECUTE IMMEDIATE
        'UPDATE ' || ref.referencing_table || ' r ' ||
        'SET ' || ref.rowid_column || ' = (' ||
        '  SELECT ROWIDTOCHAR(ROWID) FROM ' || p_table_name || ' t ' ||
        '  WHERE t.' || ref.key_column || ' = r.' || ref.key_column || ')' ||
        'WHERE ' || ref.rowid_column || ' IS NOT NULL';

      v_refresh_count := v_refresh_count + SQL%ROWCOUNT;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Refreshed ' || v_refresh_count || ' ROWID references');
    COMMIT;
  END refresh_rowids_after_reorg;

  PROCEDURE migrate_from_rowid_access(p_table_name VARCHAR2) AS
  BEGIN
    -- Log migration start
    INSERT INTO migration_log (table_name, migration_type, start_date)
    VALUES (p_table_name, 'ROWID_TO_KEY', SYSDATE);

    -- Create backup of ROWID references
    EXECUTE IMMEDIATE
      'CREATE TABLE ' || p_table_name || '_rowid_backup AS ' ||
      'SELECT * FROM ' || p_table_name || ' WHERE rowid_column IS NOT NULL';

    -- Update queries to use primary key instead
    -- This would be application-specific

    DBMS_OUTPUT.PUT_LINE('Migration initiated for ' || p_table_name);
    COMMIT;
  END migrate_from_rowid_access;

END rowid_manager;
/

-- Create supporting tables
CREATE TABLE rowid_change_log (
  log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  table_name VARCHAR2(30),
  key_value VARCHAR2(100),
  old_rowid VARCHAR2(18),
  new_rowid VARCHAR2(18),
  change_date DATE
);

CREATE TABLE rowid_reference_registry (
  registry_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  referencing_table VARCHAR2(30),
  rowid_column VARCHAR2(30),
  referenced_table VARCHAR2(30),
  key_column VARCHAR2(30),
  is_active CHAR(1) DEFAULT 'Y',
  registered_date DATE DEFAULT SYSDATE
);

2. Implement Alternative Access Methods

Use Primary Keys Instead of ROWIDs

-- Create view that provides stable identifiers
CREATE OR REPLACE VIEW stable_employee_access AS
SELECT
  employee_id as stable_id,
  first_name,
  last_name,
  department_id,
  salary,
  ROWIDTOCHAR(ROWID) as current_rowid  -- For reference only
FROM employees;

-- Create function for efficient row access
CREATE OR REPLACE FUNCTION get_employee_by_id(
  p_employee_id NUMBER
) RETURN employees%ROWTYPE AS
  v_employee employees%ROWTYPE;
BEGIN
  SELECT * INTO v_employee
  FROM employees
  WHERE employee_id = p_employee_id;

  RETURN v_employee;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

-- Create index for fast primary key access
CREATE UNIQUE INDEX emp_pk_idx ON employees(employee_id)
TABLESPACE indexes_ts
PARALLEL 4
NOLOGGING;

3. Monitoring and Prevention

Create Monitoring System

-- Create monitoring job for ROWID validity
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_ROWID_VALIDITY',
    job_type => 'PLSQL_BLOCK',
    job_action => '
      DECLARE
        v_invalid_count NUMBER;
      BEGIN
        v_invalid_count := rowid_manager.validate_all_rowids;
        IF v_invalid_count > 0 THEN
          -- Send alert
          DBMS_OUTPUT.PUT_LINE(''Alert: '' || v_invalid_count ||
                             '' invalid ROWIDs detected'');
        END IF;
      END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=6',
    enabled => TRUE
  );
END;
/

-- Create trigger to prevent ROWID storage
CREATE OR REPLACE TRIGGER prevent_rowid_storage
BEFORE INSERT OR UPDATE ON sensitive_table
FOR EACH ROW
DECLARE
  v_contains_rowid BOOLEAN := FALSE;
BEGIN
  -- Check if any column contains ROWID pattern
  IF REGEXP_LIKE(:NEW.data_column, '[A-Z0-9]{18}') THEN
    -- Potential ROWID detected
    BEGIN
      -- Try to convert to ROWID
      DECLARE
        v_test ROWID;
      BEGIN
        v_test := CHARTOROWID(:NEW.data_column);
        v_contains_rowid := TRUE;
      EXCEPTION
        WHEN OTHERS THEN
          v_contains_rowid := FALSE;
      END;
    END;

    IF v_contains_rowid THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Storing ROWIDs is not recommended. Use primary keys instead.');
    END IF;
  END IF;
END;
/

Prevention Strategies

1. Design Best Practices

-- Use primary keys for stable references
ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

-- Create surrogate keys where needed
CREATE SEQUENCE emp_seq START WITH 1000;

ALTER TABLE employees ADD (
  stable_id NUMBER DEFAULT emp_seq.NEXTVAL,
  CONSTRAINT emp_stable_uk UNIQUE (stable_id)
);

-- Document ROWID usage
COMMENT ON COLUMN app_table.rowid_column IS
  'WARNING: Contains ROWIDs - must be refreshed after table reorg';

2. Application Best Practices

// Java example - Avoid ROWID dependency
public class EmployeeDAO {

    // Bad practice - using ROWID
    @Deprecated
    public Employee getByRowId(String rowId) {
        String sql = "SELECT * FROM employees WHERE ROWID = ?";
        // This will break after table reorganization
    }

    // Good practice - using primary key
    public Employee getById(Long employeeId) {
        String sql = "SELECT * FROM employees WHERE employee_id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setLong(1, employeeId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToEmployee(rs);
            }
        }
        return null;
    }

    // If ROWID is needed for performance, refresh it
    public String getCurrentRowId(Long employeeId) {
        String sql = "SELECT ROWIDTOCHAR(ROWID) FROM employees WHERE employee_id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setLong(1, employeeId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            }
        }
        return null;
    }
}

3. Maintenance Procedures

-- Create procedure for safe table reorganization
CREATE OR REPLACE PROCEDURE safe_table_reorg(
  p_table_name VARCHAR2,
  p_tablespace VARCHAR2 DEFAULT NULL
) AS
  v_sql VARCHAR2(4000);
BEGIN
  -- Step 1: Backup ROWID mappings
  v_sql := 'CREATE TABLE ' || p_table_name || '_rowid_map AS ' ||
           'SELECT ROWIDTOCHAR(ROWID) old_rowid, t.* ' ||
           'FROM ' || p_table_name || ' t';
  EXECUTE IMMEDIATE v_sql;

  -- Step 2: Perform reorganization
  IF p_tablespace IS NOT NULL THEN
    v_sql := 'ALTER TABLE ' || p_table_name || ' MOVE TABLESPACE ' || p_tablespace;
  ELSE
    v_sql := 'ALTER TABLE ' || p_table_name || ' MOVE';
  END IF;
  EXECUTE IMMEDIATE v_sql;

  -- Step 3: Rebuild indexes
  FOR idx IN (
    SELECT index_name
    FROM user_indexes
    WHERE table_name = UPPER(p_table_name)
      AND status = 'UNUSABLE'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
  END LOOP;

  -- Step 4: Update ROWID references
  rowid_manager.refresh_rowids_after_reorg(p_table_name);

  -- Step 5: Cleanup
  EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name || '_rowid_map';

  DBMS_OUTPUT.PUT_LINE('Table ' || p_table_name || ' reorganized successfully');
END;
/

Related Errors

• ORA-01445 - Cannot select ROWID from a join view
• ORA-01446 - Cannot select ROWID from view with DISTINCT, GROUP BY
• ORA-00918 - Column ambiguously defined
• ORA-08103 - Object no longer exists

Quick Reference

Emergency Response Steps

1. ✓ Verify ROWID format is valid
2. ✓ Check if row still exists in table
3. ✓ Verify table hasn’t been reorganized
4. ✓ Check for recent DDL operations
5. ✓ Update or remove invalid ROWID references
6. ✓ Consider switching to primary key access

Quick Commands

-- Validate ROWID format
SELECT CHARTOROWID('AAAR3sAAEAAAACXAAA') FROM dual;

-- Check if ROWID exists
SELECT COUNT(*) FROM your_table WHERE ROWID = CHARTOROWID('your_rowid');

-- Get ROWID components
SELECT
  DBMS_ROWID.ROWID_OBJECT(ROWID),
  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),
  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
FROM your_table WHERE ROWNUM = 1;

-- Find current ROWID for a key
SELECT ROWIDTOCHAR(ROWID) FROM your_table WHERE primary_key = value;

-- Update invalid ROWIDs
UPDATE app_table SET rowid_col = NULL WHERE rowid_col IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM target_table WHERE ROWID = CHARTOROWID(rowid_col));

Prevention Guidelines

• Avoid storing ROWIDs - They’re not permanent
• Use primary keys - Stable across reorganizations
• Document ROWID usage - Mark where ROWIDs are used
• Refresh after maintenance - Update ROWIDs after table moves
• Monitor validity - Regular checks for invalid ROWIDs
• Plan for changes - ROWIDs change during many operations