Using dbms_repair to Find Corrupt Blocks

September 16,  2003
Don Burleson
 

dbms_repair.CHECK_OBJECT (
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
repair_table_name IN VARCHAR2 DEFAULT 'REPAIR_TABLE',
flags IN BINARY_INTEGER DEFAULT NULL,
relative_fno IN BINARY_INTEGER DEFAULT NULL,
block_start IN BINARY_INTEGER DEFAULT NULL,
block_end IN BINARY_INTEGER DEFAULT NULL,
corrupt_count OUT BINARY_INTEGER);

• schema_name – Schema name of the object to be checked for corruption.

• object_name – Name of the table or index that will be checked for corruption.

• partition_name – Partition or sub-partition name to be checked.

• object_type – Either TABLE_OBJECT or INDEX_OBJECT as specified as an enumeration (dbms_repair.table_object).

• repair_table_name – The name of the repair table to be populated in the SYS schema.

• flags – Not used.

• relative_fno – The relative file number to be used when specifying a block range to be checked.

• block_start – The first block in the block range to begin checking.

• block_end – The last block in the block range to check.

• corrupt_count – The number of corrupt blocks discovered.

The code below will check the scott.employee table for corruption and report the number of corrupted blocks.

dbms_repair.sql

set serveroutput on
declare corr_count binary_integer;
begin
corr_count := 0;
dbms_repair.CHECK_OBJECT (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
partition_name => null,
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TEST',
flags => null,
relative_fno => null,
block_start => null,
block_end => null,
corrupt_count => corr_count
);
dbms_output.put_line(to_char(corr_count));
end;
/

# Corrupt Blocks =0

PL/SQL procedure successfully completed.

Once executed, the table repair_test can be queried in order to find more about corrupt blocks. In this case, no rows exist in the table. The repair table is only populated if the check_object procedure did indeed find corrupt blocks, so no rows in this table is good news!
 

Deep inside the operating system executables there are many utilities at the fingertips of Oracle professionals, but until now there has been no advice on how to use these utilities. From tnsping.exe to dbv.exe to wrap.exe, Dave Moore describes each utility and has working examples in the online code depot. Your time savings from a single script is worth the price of this great book.

Get your copy of Oracle Utilities: Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More today and receive immediate access to the Online Code Depot!

http://www.rampant-books.com/book_2003_1_utils.htm

Regards,

    Don Burleson
    www.dba-oracle.com
    www.remote-dba.net