Part 2 : Identify Corrupted Database Object

After collection corrutpion report in V$DATABASE_BLOCK_CORRUPTION

when I run the query:


SQL > select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
----- -----  ------  ----------------   ------------- 
64    57306     1      42451322513        CORRUPT

64    57148     1      41767312306        CORRUPT
64    57132     1      41767312306        CORRUPT

Step 1 : Create base table


create table pradipta.extent_report
as
select owner,segment_name,partition_name,segment_type,tablespace_name,file_id,block_id
from
dba_extents where rownum < 0;

Step 2 :  Run Segment Identifier


BEGIN
for c1 in (select file#,block# from V$DATABASE_BLOCK_CORRUPTION)
LOOP
insert into pradipta.extent_report
(select owner,segment_name,partition_name,segment_type,tablespace_name,file_id,block_id
from
dba_extents where file_id = c1.file# and c1.block# between block_id and
block_id +
blocks - 1);
END LOOP;
commit;
END;
/


Step 3: Check corrupted segments

PDD    IDX_PAT_01   ORG0330    INDEX PARTITION     IDX_PDD     64    57093

PDD    IDX_PAT_01   ORG0330    INDEX PARTITION     IDX_PDD     64    57093
PDD    IDX_PAT_01   ORG0330    INDEX PARTITION     IDX_PDD     64    57093