Rem Filename   : redo.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : V8.x.x
Rem
Rem Modification history
Rem --------------------
Rem 06-Feb-2002   Initial creation
Rem 
Rem =============================================================================================
Rem
Rem Description : Lists the online redo logs, based on information that can 
Rem               be found in the control file(s).
Rem 
Rem Sample output
Rem -------------
Rem                        Sequence Timestamp of
Rem Group Members Size(Kb)   Number lowest SCN                Lowest SCN Archived Status
Rem ----- ------- -------- -------- -------------------- --------------- -------- ---------------
Rem     1       2  102.400     1609 06-Feb-2002 16:45:17   4.353.266.281 YES      INACTIVE
Rem     2       2  102.400     1610 06-Feb-2002 16:56:43   4.353.271.455 YES      INACTIVE
Rem     3       2  102.400     1611 06-Feb-2002 17:34:18   4.353.275.509 NO       CURRENT
Rem
Rem =============================================================================================
 
SET PAGESIZE   9999
SET TRIMSPOOL  ON
SET FEEDBACK   OFF

COLUMN group#           FORMAT 9999           HEADING "Group"
COLUMN members          FORMAT 999999         HEADING "Members"
COLUMN SizeKB           FORMAT 999G999        HEADING "Size(Kb)"
COLUMN sequence#        FORMAT 999999         HEADING "Sequence|  Number"
COLUMN FirstTime        FORMAT A20            HEADING "Timestamp of|lowest SCN"
COLUMN first_change#    FORMAT 99G999G999G999 HEADING "Lowest SCN"
COLUMN status           FORMAT A15            HEADING "Status"
COLUMN archived         FORMAT A8             HEADING "Archived"

alter session set nls_numeric_characters = ',.';

SELECT group#,
       members,
       bytes/1024 "SizeKB",
       sequence#, 
       to_char(first_time,'DD-Mon-YYYY HH24:MI:SS') "FirstTime",
       first_change#, 
       archived,
       status
  FROM v$log
 ORDER BY group#;

SET FEEDBACK   ON
PROMPT