LLI Wiki — My Digital Knowledge Base

Oracle: list corrupt blocks with V$DATABASE_BLOCK_CORRUPTION

Spot corrupt blocks in an Oracle database and in RMAN backups using two dynamic views.

When corruption errors show up in the alert log or in trace files, two views tell you which blocks are affected.

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SELECT * FROM V$BACKUP_CORRUPTION;

Sample output for the first view:

FILE#   BLOCK#  BLOCKS CORRUPTION_CHANGE# CORRUPTIO
------ -------- ------ ------------------ ---------
    49   515632      1         8.3019E+12 LOGICAL
    49   463340      5         8.3019E+12 LOGICAL

To find the affected segment, join FILE# and BLOCK# against DBA_EXTENTS.

Mastodon LLIMastodon SocialFosstodon