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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s