GV$DATABASE_BLOCK_CORRUPTION

select
distinct                 -- INST_ID NUMBER
  userenv ('Instance'),
file#,                   -- FILE# NUMBER
block#,                  -- BLOCK# NUMBER
blocks,                  -- BLOCKS NUMBER
corruption_change#,      -- CORRUPTION_CHANGE# NUMBER
corruption_type          -- CORRUPTION_TYPE VARCHAR2(9)
from
(
select
file#,
block#,
blocks,
corruption_change#,
copy_stamp stamp,
corruption_type 
from
v$copy_corruption 
union select
file#,
block#,
blocks,
corruption_change#,
bs.stamp,
corruption_type 
from
v$backup_corruption bc,
v$backup_set bs 
where
bc.set_count = bs.set_count and bc.set_stamp = bs.set_stamp) outer 
where
not exists (
select
1 
from
v$datafile_copy 
where
scanned = 'YES' and outer.file# = file# and outer.stamp < stamp 
union select
1 
from
v$backup_datafile bdf,
v$backup_set bs 
where
bdf.set_count = bs.set_count and bdf.set_stamp = bs.set_stamp and outer.file# = file# and outer.stamp < bs.stamp)
home |  up  |  mailbox