Rem Filename   : logsw.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : V8.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 29-Nov-2001  Initial creation.
Rem 17-Nov-2003  Enlarge output format of the SCN columns.
Rem
Rem ===========================================================================================
Rem
Rem Description : Lists all redo log switch info contained in the control file(s).
Rem
Rem               Timestamp of lowest SCN : Date and time of the first entry in the log. This
Rem                                         corresponds with the lowest CSN in the file.
Rem               Sequence number         : The sequence number of the archive log.
Rem               Lowest SCN              : Lowest System Change Number in the log.
Rem               Highest SCN             : Highest System Change Number in the log.
Rem
Rem Sample output
Rem -------------
Rem Timestamp of         Sequence
Rem lowest SCN             Number          Lowest SCN         Highest SCN
Rem -------------------- -------- ------------------- -------------------
Rem 08-Sep-2003 14:44:26     5947   2.762.702.619.047   2.762.702.625.723
Rem 08-Sep-2003 15:21:34     5948   2.762.702.625.723   2.762.702.631.180
Rem 08-Sep-2003 16:07:54     5949   2.762.702.631.180   2.762.702.651.834
Rem 08-Sep-2003 16:33:49     5950   2.762.702.651.834   2.762.702.680.934
Rem 08-Sep-2003 16:57:58     5951   2.762.702.680.934   2.762.702.686.639
Rem 08-Sep-2003 17:13:32     5952   2.762.702.686.639   2.762.702.691.864
Rem 08-Sep-2003 17:26:41     5953   2.762.702.691.864   2.762.702.698.070
Rem ...
Rem ===========================================================================================

SET PAGESIZE    9999
SSET TRIMSPOOL  ON

COLUMN sequence#        FORMAT 999999             HEADING "Sequence|  Number"
COLUMN FirstTime        FORMAT A20                HEADING "Timestamp of|lowest SCN"
COLUMN first_change#    FORMAT 99G999G999G999G999 HEADING "Lowest SCN"
COLUMN next_change#     FORMAT 99G999G999G999G999 HEADING "Highest SCN"

SET FEEDBACK   OFF
alter session set nls_numeric_characters = ',.';
SET FEEDBACK   ON

SELECT to_char(first_time,'DD-Mon-YYYY HH24:MI:SS') "FirstTime",
       sequence#, 
       first_change#, 
       next_change#
  FROM v$log_history
 ORDER BY sequence#;