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 e.file_id,
FROM dba_extents e
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
Spool off

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

Edward Ramamoorthy

I work in one of the top 10 tech company in India. In my spare time I write for

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 article or provide a link back to this page.