SQL QUERY TO FIND FUZZINESS IN DATAFILE HEADER

Use the below query to find the consistency between controlfile header and datafile header!

set lines 200 pages 1000
col file# for 9999999
select
c_h.file#,
case 
when c_h.CHECKPOINT_CHANGE# > d_h.CHECKPOINT_CHANGE# then 'Fuzzy header!'
when c_h.CHECKPOINT_CHANGE# = d_h.CHECKPOINT_CHANGE# then 'ACID complaint!'
when c_h.CHECKPOINT_CHANGE# < d_h.CHECKPOINT_CHANGE# then 'Media recovery required!'
end Consistency_check
from
v$datafile c_h
inner join v$datafile_header d_h on (c_h.FILE#=d_h.FILE#)
order by 1;

Leave a Reply