Find & repair Corrupt block in Oracle database

~ Find & repair Corrupt block in Oracle
~ Applies to any Oracle version
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Find if any block (Table) is corrupted.

SQL> select * from V$DATABASE_BLOCK_CORRUPTION -- will show if any corruped block

Below query can give you information about corrupted block:

set head on; 
set pagesize 2000 
set linesize 250 
select * from v$database_block_corruption;
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# + c.blocks - 1 
AND e.block_id + e.blocks - 1 >= c.block# 
UNION 
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# 
, header_block corr_start_block# 
, header_block corr_end_block# 
, 1 blocks_corrupted 
, 'Segment Header' description 
FROM dba_segments s, v$database_block_corruption c 
WHERE s.header_file = c.file# 
AND s.header_block between c.block# and c.block# + c.blocks - 1 
UNION 
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# 
, greatest(f.block_id, c.block#) corr_start_block# 
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# 
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
- greatest(f.block_id, c.block#) + 1 blocks_corrupted 
, 'Free Block' description 
FROM dba_free_space f, v$database_block_corruption c 
WHERE f.file_id = c.file# 
AND f.block_id <= c.block# + c.blocks - 1 
AND f.block_id + f.blocks - 1 >= c.block# 
order by file#, corr_start_block#; 

1) Collect file ids
sql> select distinct file_id from dba_extents;
2) Collect details
sql>
SELECT file_id,segment_name,segment_type,owner,tablespace_name,block_id,blocks
FROM   sys.dba_extents
WHERE  (file_id between 1 and 23) AND 658438 BETWEEN block_id AND block_id + blocks - 1;
3) Repair - If Table
a) Collect all data to temporary table and collect all DDL script and grants.
b) drop the table and re-create it with DDL script. (Disable refence key before drop, enable after create table)
c) Insert all records to the table

Note: This entire activity should not be taken in prod databases without Oracle support's recommendation.

Click here to get my work-around which is posted to this blog.

Hope this will help to resolve block corruption issue.