After you have validated database as shown in previous post any corrupt database blocks will be reported in the view V$DATABASE_BLOCK_CORRUPTION. Now you want to find out the tables that used to have data in these blocks; those data have typically been lost if you have no backup of them or do not posses special tools like DUDE .
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](https://support.oracle.com/) 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;
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.