Applies to:
Oracle Server - Enterprise Edition - Version 8.1.5.0 to 11.2.0.3 [Release 8.1.5 to 11.2]
Purpose
This script is intended to provide a simple and quick way to run DBMS_REPAIR to identify and skip corrupted blocks
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.
Reference:
DBMS_REPAIR SCRIPT [ID 556733.1]
