Oracle – Find the Block Corruption Object Name & Owner

To find the block corruption object name & owner

Step 1

To find the block corruption in database.

SQL> select count(*) from v$database_block_corruption;

Return more than 0 rows go to steps 2.

Step 2

Below Query is used to generate the dynamic sql statements.

set pages 200
set lines 200
set heading off
SQL>Spool Block_corruption_list.log
select
‘SELECT e.file_id,
e.block_id,
e.owner,
e.segment_name,
e.segment_type
FROM dba_extents e
WHERE
file_id=’|| file#||’ and ‘|| block#||’ BETWEEN block_id AND block_id + blocks – 1; ‘
from v$database_block_corruption order by file#;
spool off

Step 3

We run above “Block_corruption_list.log” files.

SQL> Spool Block_corruption_objectname.log
SQl>@Block_corruption_list.log
Spool off

I Hope this article helped to you. Suggestions are welcome.