每次运行 RMAN backup validate 命令，视图 V$DATABASE_BLOCK_CORRUPTION 中坏块信息都会被更新。了解视图内容，参见 Oracle Database Reference 文档。
步骤2：识别损坏的段
下面的查询可以将视图 v$database_block_corruption 中记录的坏块匹配到对应的段或者是空闲块。
$ sqlplus / as sysdba
set pagesize 2000
set linesize 250
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
输出例子：
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE EMP 6 10 10 1
SCOTT TABLE PARTITION ORDER ORDER_JAN 6 26 28 3
6 29 29 1 Free Block
SCOTT TABLE BONUS 6 34 34 1
6 35 35 1 Free Block
SCOTT TABLE DEPT 6 42 42 1 Segment Header
SCOTT TABLE INVOICE 6 50 50 1
注意:
如果坏块出现在字典管理的表空间，并且段头块损坏，上面的查询可能显示同一个块 2 次。
如果 ASSM 表空间的段头块损坏，那么上面的查询会显示该段头块，但是相同对象上的其他坏块可能就不会再显示。
--------------------------------------推荐阅读 --------------------------------------
--------------------------------------分割线 --------------------------------------