oracle_sql_检查未备份就被删除的归档日志

select thread#,row_number() over(partition by thread# order by min(sequence#)) seq,
        min(sequence#) init_log, max(sequence#) max_log, min(first_time) first_time,max(first_time) last_time,
        max(sequence#) - min(sequence#)+1 total from
(select thread#,sequence#,first_time,sequence# - seq gap
from (
select a.thread#,a.sequence#, a.first_time,
        row_number() over(partition by a.thread# order by a.sequence#) seq
from v$archived_log a, v$backup_archivelog_details b
where a.completion_time >= (select min(start_time) from v$backup_set_details where incremental_level=0) and dest_id=1
    and a.deleted = 'YES'
    and a.thread#=b.thread#(+) and a.sequence#=b.sequence#(+)
    and b.sequence# is null
)) group by thread#,gap order by 2;