Handling Block Corruption

Run dbverify against the datafile
This will output the corrupt blocks
Use the block id's in the following pl/sql code This will output the segments that are corrupted

declare
n1 number;
n2 number;
b1 number;
b2 number;
v_file_id number := 10;   ---  The file id of the corrupted file
function is_corrupted(b1 number, b2 number) return number IS
begin
for n2 in b1..b2 loop
if n2 in(97317,97318,97319,97320,97333,97334,97335,97336,97349,97350,97351,97352,97521,97522,97523,97524,97537,97538,97539,97540,97553,
97554,97555,97556,101413,101414,101415,101416,101429,101430,101431,101432,101445,101446,101447,101448,101617,101618,101619,
101620,101633,101634,101635,101636,101649,101650,101651,101652,166085,166086,166087,166088,166101,166102,166103,166104,166117,
166118,166119,166120,166133,166134,166135,166136,166149,166150,166151,166152,166165,166166,166167,166168,170181,170182,
170183,170184,170197,170198,170199,170200,170213,170214,170215,170216,170229,170230,170231,170232,170245,170246,170247,
170248,170261,170262,170263,170264) then return(1); end if;
end loop;
return(0);
end;
begin
for cur1 in(select owner, segment_name, partition_name,block_id, blocks from dba_extents where file_id=v_file_id order by block_id) LOOP
n1 := is_corrupted(cur1.block_id, cur1.block_id+cur1.blocks);
if n1=1 then
dbms_output.put_line(cur1.owner||'  '||cur1.segment_name||'  '||cur1.partition_name);
end if;
END LOOP;
end;
/


For LOB segments the following query can be used to obtain the table names


select owner, table_name, column_name from dba_lobs
where segment_name in(
'SYS_LOB0000069622C00010$$', 
'SYS_LOB0000069622C00029$$', 
'SYS_LOB0000070456C00010$$', 
'SYS_LOB0000070456C00029$$');