Before you start I recommend to purge the DBA Recycle Bin if it is in use, as reported here certain queries seem to take longer time when it is not empty. Execute as SYSDBA:
purge dba_recyclebin;Then create a temporary table with information on the corrupt blocks:
create table segments_with_corruption as select FILE_ID,BLOCK_ID,blocks,OWNER,SEGMENT_NAME, start_corrupt,corrupt_blocks from ( select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME, c.block# start_corrupt,c.blocks corrupt_blocks from DBA_EXTENTS E, V$DATABASE_BLOCK_CORRUPTION C where E.BLOCK_ID <= C.block# and E.BLOCK_ID + E.blocks - 1 >= C.block# and e.file_id=c.file# union all select e.FILE_ID,e.BLOCK_ID,e.blocks,e.OWNER,e.SEGMENT_NAME, c.block# start_corrupt,c.blocks corrupt_blocks from DBA_EXTENTS E, V$DATABASE_BLOCK_CORRUPTION C where E.BLOCK_ID >= C.block# and E.BLOCK_ID <= C.block# + C.blocks -1 and e.file_id=c.file# );
This query may take a long time to run, which is a reason to materialize it in a table while you are working on this. You can find a more advanced query on MOS in article 472231.1 To create this query I made a drawing for myself with all different scenarios on how interval of corrupt blocks could possible overlap with extents belonging to some object. Then I wrote down the criteria and merged them together.
Use this query to find owner, name and type of the segments you have to work with:
select distinct c.owner,c.segment_name, c.segment_type from segments_with_corruption c, dba_segments s where c.segment_name=c.segment_name and c.owner=s.owner;For segment_type=INDEX you can fix it by simply rebuilding the index (hopefully none of the rows it indexes are in corrupt blocks).
The next post will be on how to salvage as much as possibly from the good blocks belonging to a table that has some corrupt blocks. I'll show a query you will run to generate other statements that will be used to extract data from blocks around the corrupt blocks.