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
V$DATABASE_BLOCK_CORRUPTIONlists the corrupt blocks known to the database (file number, first block, number of blocks, corruption type). It is populated notably by RMAN validate and backup commands.V$BACKUP_CORRUPTIONlists corruptions detected in RMAN backups.- Here the corruption is of type
LOGICALon datafile number 49.
To find the affected segment, join FILE# and BLOCK# against DBA_EXTENTS.