Oracle DBMS_REPAIR

Oracle Database provides different methods for detecting and correcting data block corruption. another way to manage data block corruption is to use the dbms_repair package.

we can use dbms_repair to detect and repair corrupt blocks in tables and indexes.

There is some Limitation and Restrictions for dbms_repair package.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/repair002.htm#sthref2281

Let see the step how to use dbms_repair package to detect and repair corrupt block in the tables and indexes.

There is two tables which must be created before using dbms_repair package.
1. Repair_Table
2. Orphan_Key_Table

NOTE: Both table must be created in SYS schema.

There is two main procedure of dbms_repair package.

1. check_object procedure

which checks and reports block corruptions for a specified objects

2. Admin_table procedure

This procedure create two tables which populate by check_object and store information about block corruption.

Table Creation through ADMIN_TABLE procedure

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> BEGIN

2 DBMS_REPAIR.ADMIN_TABLES (

3 TABLE_NAME => 'REPAIR_TABLE',

4 TABLE_TYPE => dbms_repair.repair_table,

5 ACTION => dbms_repair.create_action,

6 TABLESPACE => 'USERS');

7 END;

8 /
PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPAIR.ADMIN_TABLES (

3 TABLE_NAME => 'ORPHAN_KEY_TABLE',

4 TABLE_TYPE => dbms_repair.orphan_table,

5 ACTION => dbms_repair.create_action,

6 TABLESPACE => 'USERS');

7 END;

8 /
PL/SQL procedure successfully completed.

1. Detect corruptions

SQL> set serveroutput on

SQL> declare num_corrupt int;

2 begin

3 num_corrupt :=0;

4 dbms_repair.check_object (

5 schema_name => 'SCOTT',

6 object_name => 'DEPT',

7 repair_table_name => 'REPAIR_TABLE',

8 corrupt_count => num_corrupt);

9 dbms_output.put_line ('number corrupt: ' to_char(num_corrupt));

10 end;

11 /

number corrupt: 0

PL/SQL procedure successfully completed.

After that query in repair_table table for check more information