Detect and repair corrupt table using dbms_repair package.

To use dbms_repair to repair a corrupt table, first you create a repair table which will store data
about corruptions found by CHECK_OBJECT procedure.

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

Use check_object procedure to check if a table has any corruptions. For example, to check if the
hr.employees table has any corruptions, run the query below.

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'HR',
OBJECT_NAME => 'EMPLOYEES',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('Corrupt ' || TO_CHAR (num_corrupt));
END;
/

Query the repair table for more information about corrupt blocks.


SELECT OBJECT_NAME,CORRUPT_DESCRIPTION,REPAIR_DESCRIPTION FROM REPAIR_TABLE;

Fix the corrupt blocks.

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'HR',
OBJECT_NAME=> 'EMPLOYEES',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('FIxed: ' || TO_CHAR(num_fix));
END;
/


example

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'HR',
OBJECT_NAME=> 'EMPLOYEES',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('FIxed: ' || TO_CHAR(num_fix));
END;
/