Data corruption is one of the most critical challenges in any Oracle database environment. Even with strong backup and recovery strategies, there are times when you need to identify, isolate, and continue operations while planning a full recovery.

This is where Oracle DBMS_REPAIR becomes a powerful tool.

In this guide, I’ll walk you through a practical, human-friendly explanation of how to use Oracle DBMS_REPAIR to detect and repair corrupt blocks, rebuild indexes, and safely continue database operations.

---

What is DBMS_REPAIR?

Oracle DBMS_REPAIR is an Oracle-supplied PL/SQL package designed to:

• Detect corrupted data blocks
• Mark corrupted blocks so Oracle skips them during queries
• Identify and fix orphan index entries
• Allow continued database operations despite corruption

⚠️ Important: It does not recover lost data — it only isolates corruption so your system remains usable.

---

Step 1 – Create Administration Tables

Before you begin, Oracle needs two internal tables:

• REPAIR_TABLE → stores corrupt block details
• ORPHAN_KEY_TABLE → stores invalid index entries

BEGIN
  DBMS_REPAIR.admin_tables (
    table_name => 'REPAIR_TABLE',
    table_type => DBMS_REPAIR.repair_table,
    action     => DBMS_REPAIR.create_action,
    tablespace => 'USERS');

  DBMS_REPAIR.admin_tables (
    table_name => 'ORPHAN_KEY_TABLE',
    table_type => DBMS_REPAIR.orphan_table,
    action     => DBMS_REPAIR.create_action,
    tablespace => 'USERS');
END;
/

These tables act like your repair workspace.

---

Step 2 – Check Table for Corruption

Now scan your table using CHECK_OBJECT.

SET SERVEROUTPUT ON
DECLARE
  v_num_corrupt INT;
BEGIN
  DBMS_REPAIR.check_object (
    schema_name       => 'SCOTT',
    object_name       => 'DEPT',
    repair_table_name => 'REPAIR_TABLE',
    corrupt_count     => v_num_corrupt);

  DBMS_OUTPUT.put_line('number corrupt: ' || TO_CHAR(v_num_corrupt));
END;
/

If v_num_corrupt > 0, you can check details in:

• CORRUPTION_DESCRIPTION
• REPAIR_DESCRIPTION

inside the REPAIR_TABLE.

---

Step 3 – Mark Corrupt Blocks

At this point, Oracle knows which blocks are corrupt — but it doesn’t yet skip them.

Use:

SET SERVEROUTPUT ON
DECLARE
  v_num_fix INT;
BEGIN
  DBMS_REPAIR.fix_corrupt_blocks (
    schema_name       => 'SCOTT',
    object_name       => 'DEPT',
    object_type       => DBMS_REPAIR.table_object,
    repair_table_name => 'REPAIR_TABLE',
    fix_count         => v_num_fix);

  DBMS_OUTPUT.put_line('num fix: ' || TO_CHAR(v_num_fix));
END;
/

This step marks the blocks so they can be ignored during DML operations.

---

🔗 Step 4 – Identify Orphan Index Keys

Indexes might still point to corrupted blocks.

Use:

SET SERVEROUTPUT ON
DECLARE
  v_num_orphans INT;
BEGIN
  DBMS_REPAIR.dump_orphan_keys (
    schema_name       => 'SCOTT',
    object_name       => 'PK_DEPT',
    object_type       => DBMS_REPAIR.index_object,
    repair_table_name => 'REPAIR_TABLE',
    orphan_table_name => 'ORPHAN_KEY_TABLE',
    key_count         => v_num_orphans);

  DBMS_OUTPUT.put_line('orphan key count: ' || TO_CHAR(v_num_orphans));
END;
/

👉 If orphan keys exist → rebuild the index

ALTER INDEX PK_DEPT REBUILD;

---

Step 5 – Rebuild Freelist Structure

Corrupt blocks can break freelist access for following blocks.

Fix it using:

BEGIN
  DBMS_REPAIR.rebuild_freelists (
    schema_name => 'SCOTT',
    object_name => 'DEPT',
    object_type => DBMS_REPAIR.table_object);
END;
/

---

Step 6 – Skip Corrupt Blocks in Queries

Now tell Oracle to ignore corrupt blocks during operations:

BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'SCOTT',
    object_name => 'DEPT',
    object_type => DBMS_REPAIR.table_object,
    flags       => DBMS_REPAIR.skip_flag);
END;
/

You can verify using:

SELECT SKIP_CORRUPT FROM DBA_TABLES WHERE TABLE_NAME='DEPT';

---

Final Result

At this stage:

✔ Corrupt blocks are isolated
✔ Indexes are clean
✔ Database operations can continue

But remember:

⚠️ Data in those blocks is still lost — you must restore or reload it later.

---

Testing Scenario – Simulating Datafile Corruption

To understand DBMS_REPAIR practically, you can simulate corruption in a controlled environment.

---

Create Test Tablespace and Object

CREATE TABLESPACE corrupt_test 
DATAFILE '/u01/app/oracle/oradata/testdb/corrupt_test01.dbf' SIZE 100M;

CREATE USER test_user IDENTIFIED BY password DEFAULT TABLESPACE corrupt_test;

GRANT create session, resource, create table TO test_user;

CONN test_user/password;

CREATE TABLE test_table (
  id NUMBER,
  data VARCHAR2(100)
) TABLESPACE corrupt_test;

INSERT INTO test_table VALUES (1, 'This is a test block to corrupt.');
COMMIT;

---

Take RMAN Backup

rman target /
BACKUP TABLESPACE corrupt_test;

---

Identify Block Location

SELECT DBMS_ROWID.rowid_relative_fno(ROWID) AS file_no,
       DBMS_ROWID.rowid_block_number(ROWID) AS block_no,
       id, data
FROM test_table;

Example output:

file 6, block 131

---

Flush Buffer Cache

sqlplus "/ as sysdba"
ALTER SYSTEM FLUSH BUFFER_CACHE;

---

Corrupt Block at OS Level

dd of=/u01/app/oracle/oradata/testdb/corrupt_test01.dbf \
bs=8192 conv=notrunc seek=131 <<EOF
CORRUPTED DATA HERE
EOF

---

Verify Corruption

CONN test_user/password;
SELECT * FROM test_table;

Expected error:

ORA-01578: ORACLE data block corrupted

---

Best Practices for Handling Corruption

✔ Always take RMAN backups regularly
✔ Use DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
✔ Monitor alert logs frequently
✔ Run ANALYZE TABLE VALIDATE STRUCTURE for checks
✔ Use DBMS_REPAIR only as a temporary workaround

---

Final Thoughts

DBMS_REPAIR is a lifesaver tool when dealing with block corruption in production environments. It helps you:

• Keep systems running
• Prevent full outages
• Buy time for proper recovery

However, it should always be part of a larger backup and recovery strategy, not a permanent fix.