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.

Help Us Grow

If you like this post, please share it with your friends.

You are free to copy and redistribute this article in any medium or format, as long as you keep the links in the articles or provide a link back to this page.

Subscribe to our mailing list