Rem Filename   : logsw_today.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 09-Oct-2002  Make the SCN columns wider.
Rem
Rem ========================================================================================
Rem
Rem Description : Lists all redo log switch info of today.
Rem               The information shown is extracted from 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
Rem Sample output
Rem -------------
Rem Timestamp of         Sequence
Rem lowest SCN             Number      Lowest SCN     Highest SCN
Rem -------------------- -------- --------------- ---------------
Rem 29-Nov-2001 00:11:56      995       8.619.523       8.626.285
Rem 29-Nov-2001 05:46:20      996       8.626.285       8.632.150
Rem 29-Nov-2001 09:07:24      997       8.632.150       8.636.545
Rem 29-Nov-2001 10:21:41      998       8.636.545       8.640.727
Rem 29-Nov-2001 11:27:47      999       8.640.727       8.642.828
Rem 29-Nov-2001 11:35:04     1000       8.642.828       8.644.971
Rem 29-Nov-2001 11:41:10     1001       8.644.971       8.647.792
Rem 29-Nov-2001 11:48:35     1002       8.647.792       8.650.291
Rem 29-Nov-2001 11:55:13     1003       8.650.291       8.652.458
Rem 29-Nov-2001 12:00:51     1004       8.652.458       8.654.833
Rem 29-Nov-2001 12:07:31     1005       8.654.833       8.656.769
Rem ...
Rem ===========================================================================================

SET PAGESIZE    9999
SET TRIMSPOOL   ON

COLUMN inst_id          FORMAT 99                 HEADING "Instancia"
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 inst_id, 
       to_char(first_time,'DD-Mon-YYYY HH24:MI:SS') "FirstTime",
       sequence#, 
       first_change#, 
       next_change#
  FROM gv$log_history
 WHERE first_time > trunc(sysdate)-&1
 ORDER BY inst_id ,first_time;

UNDEFINE 1