GV$ARCHIVE_GAP

select
USERENV ('Instance'),    -- INST_ID NUMBER
high.thread#,            -- THREAD# NUMBER
"LOW_SEQUENCE#",         -- LOW_SEQUENCE# NUMBER
"HIGH_SEQUENCE#"         -- HIGH_SEQUENCE# NUMBER
from
(
select
thread#,
min (sequence#) -1 "HIGH_SEQUENCE#" 
from
(
select
a.thread#,
a.sequence# 
from
(
select
thread#,
sequence# 
from
v$archived_log) a,
(
select
thread#,
max (sequence#) gap1 
from
v$log_history 
group by thread#) b 
where
a.thread# = b.thread# and a.sequence# > gap1) 
group by thread#) high,
(
select
thread#,
min (gap2) "LOW_SEQUENCE#" 
from
(
select
thread#,
sequence#+1 gap2 
from
v$log_history h,
v$datafile d 
where
checkpoint_change# <= next_change# and checkpoint_change# >= first_change# and enabled = 'READ WRITE') 
group by thread#) low 
where
low.thread# = high.thread# and "LOW_SEQUENCE#" <= "HIGH_SEQUENCE#" and "HIGH_SEQUENCE#" < (
select
MAX (sequence#) 
from
v$archived_log)
home |  up  |  mailbox