Checking Data Block Corruption

If you think a table might has corrupted blocks then you can check it with the DBMS_REPAIR package. The checking procedure is very simple; first create a repair table (in your schema) if you don't have one, then check the table with the DBMS_REPAIR.CHECK_OBJECT procedure, and finally check the repair table looking for block corruption records. Optionally, you can drop the repair table if you won't need it anymore.

myserver> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 20 08:29:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

PL/SQL procedure successfully completed.

SQL> exit;

myserver> nohup sqlplus '/ as sysdba' @check.sql &
[1]     12263522
myserver> Sending nohup output to nohup.out.

myserver> tail -f nohup.out

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 20 08:29:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

number corrupt: 0

PL/SQL procedure successfully completed.

SQL> exit;

myserver> cat check.sql
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
     SCHEMA_NAME => 'MYSCHEMA',
     OBJECT_NAME => 'MYTABLE',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
exit;

As you can see, according to the procedure's message there are no corrupted blocks (number corrupt: 0), and if you created the repair table just before running the DBMS_REPAIR.CHECK_OBJECT
procedure then it will be empty as well. You might noticed I decided to create an SQL script (check.sql) and run it with nohup; if you have a very large table and a not so good connection to the database server, it might be a good idea to run the procedure with the nohup command.

myserver> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 20 08:29:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from REPAIR_TABLE;

no rows selected

SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.drop_action);
END;
/
 
PL/SQL procedure successfully completed.

SQL> exit;

Adn if you ran out of luck and got a table with corrupted blocks, be sure to check the Oracle documentation and understand what means repairing a table with block corruption.