Script: Nice script to find block corruptions with the associated object

Hello

Thanks to my colleague (Satish) to share this.

Here is the nice script to find the relative object name that is having block corruption, ofcourse the the v$database_block_corruption will populate only when you run the rman command.

1) RMAN>  Backup validate check logical database;

This will populate the v$data_block_corruption with rows about the blocks 
that is involved in block corruption and the type 
in v$data_block_corruption has different values

2) Next run this statement to identify the objects associated with corruption
SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id = c.block#; 

OWNER      SEGMENT_TYPE         SEGMENT_NAME         PARTITION_NAME       FILE#
---------- -------------------- -------------------- --------------- ----------
CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED D
----------------- --------------- ---------------- -
TEST       INDEX                XXXXXXXXXXXX                         62
                                ME_IDX
           292779          292779                1 
           
           

Hope this helps

-Thanks
Geek DBA