@@header


/*
*
*  Author    : Vishal Gupta
*  Purpose   : Display archivelog gap
*  Parameter : 1 - Thread Number  (Default '%')
*              2 - From Sequence  (Default '%')
*              3 - To Sequence    (Default '%')
*  
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  04-Apr-10  Vishal Gupta  Created
*  
*/





/************************************
*  INPUT PARAMETERS
************************************/
DEFINE thread="&&1"
DEFINE from_sequence="&2"
DEFINE to_sequence="&3"

set term off
COLUMN  _thread               NEW_VALUE  thread                NOPRINT
COLUMN  _from_sequence        NEW_VALUE  from_sequence         NOPRINT
COLUMN  _to_sequence          NEW_VALUE  to_sequence           NOPRINT

SELECT DECODE('&&thread','','%','&&thread')                   "_thread"
     , DECODE('&&from_sequence','','%','&&from_sequence')     "_from_sequence"
     , DECODE('&&to_sequence','','%','&&to_sequence')         "_to_sequence"
FROM DUAL;
set term on


/************************************
*  M A I N   S C R I P T
************************************/

COLUMN sequence#         HEADING  "SEQUENCE#"         FORMAT 99999999

/*
* Not using WITH clause (aka CTE aka sub-query refactorying
*  , as it does not work on standby database in MOUNT mode and not in OPEN mode.
*/

select minmax.thread#
     , i.sequence# + minmax.min_sequence# - 1 sequence#
     --, l.thread#
     --, l.sequence# 
     , l.applied
     , l.deleted
     , l.status
     , l.archived
     , NVL(TO_CHAR(l.first_time,'DD-MON-YY HH24:MI:SS'),'   Missing    ') first_time
     , TO_CHAR(l.next_time,'DD-MON-YY HH24:MI:SS')                        next_time
     , l.fal
     , l.creator
     , l.registrar
     , TO_CHAR(l.completion_time,'DD-MON-YY HH24:MI:SS')                  completion_time
from (select rownum  sequence# 
      from dual 
      connect by level  <= 1500
     )  i
     join (select dest_id
         , thread#
         , max(DECODE(applied, 'YES',sequence#,0)) - 3 min_sequence#
         , max(sequence#) max_sequence#
         --, min(first_time)
         --, max(first_time) 
      From v$archived_log 
     where dest_id = 1 
       AND thread# like '&&thread'
       and first_time > trunc(sysdate - 1) 
     group by dest_id, thread# 
     order by thread#
    ) minmax on 1 = 1 
     LEFT OUTER JOIN v$archived_log l on l.thread# = minmax.thread# and l.sequence# = i.sequence# + minmax.min_sequence# - 1
WHERE 1=1
  --AND (l.thread# IS  NULL OR l.sequence# IS  NULL)
  and i.sequence# + minmax.min_sequence# - 1 <=  minmax.max_sequence# 
  AND minmax.thread# like '&&thread'
  AND i.sequence# + minmax.min_sequence# - 1 
         BETWEEN DECODE('&&from_sequence','%',minmax.min_sequence#,'&&from_sequence') 
             AND DECODE('&&to_sequence','%',minmax.max_sequence#,'&&to_sequence') 
order by minmax.thread#
        , i.sequence#
--order by l.first_change#
;


SELECT l.thread#
     , TO_CHAR(sysdate,'DD-MON-YY HH24:MI:SS')           current_time
     , TO_CHAR(max(l.next_time) ,'DD-MON-YY HH24:MI:SS') max_next_time
  FROM v$archived_log l
GROUP BY l.thread#
ORDER BY l.thread#
;


@@footer