Applies to:

Oracle Server - Enterprise Edition - Version 8.1.5.0 to 11.2.0.3 [Release 8.1.5 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 11-Sep-2012

Purpose

This script. is intended to provide a simple and quick way to run DBMS_REPAIR to identify and skip corrupted blocks

Requirements

SQL*Plus

Configuring

Run sqlplus with SYS user

Instructions

1. Run sqlplus. Example:

sqlplus '/ as sysdba'

2. run the script. from sqlplus

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Script

REM Create the repair table in a given tablespace:

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => '&tablespace_name');
END;
/

REM Identify corrupted blocks for schema.object:

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;

REM Mark the identified blocks as corrupted

DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/

Notes:

Recreate indexes after using DBMS_REPAIR as INDEX scan may produce errors if accessing the corrupt block. If there is an unique index, then reinserting the same data may also produce error ORA-1.
Use the dbms_repair.NOSKIP_FLAG in the FLAGS value in procedure SKIP_CORRUPT_BLOCKS if it is needed to stop skipping corrupt blocks in the object after the dbms_repair.SKIP_FLAG was used.

If the goal is to skip the corrupt blocks for a specific object, it is just needed to run procedure SKIP_CORRUPT_BLOCKS. Only blocks producing ORA-1578 will be skipped in that case. If different errors are produced then it is required to run these additional procedures: ADMIN_TABLES, CHECK_OBJECT and FIX_CORRUPT_BLOCKS
If it is needed to clear a table from corruptions and after using procedure SKIP_CORRUPT_BLOCKS, the table can be moved with: "alter table MOVE" instead of recreating or truncating it. Then use the dbms_repair.NOSKIP_FLAG described above. Note that the data inside the corrupt blocks is lost.