GV$ARCHIVE_GAP

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