find block corruption object/segment

Query to display corrupted block object/segment name
--------------------------------------------------------------------

set pages 1000 lines 180

COLUMN segment_name FORMAT A30

 

SELECT DISTINCT owner, segment_name
FROM   v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block#
BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;

 

O/P

OWNER                          SEGMENT_NAME

------------------------------ ------------------------------

SYS                            SMON_SCN_TIME_SCN_IDX