SQL Query for Oracle Redo Log Files

Handy SQL when manipulating logfile groups set pagesize 30

set linesize 100

column member format a60

column status format a10

column size_MB format '999,999'

select group#,thread#,members,status,bytes/1024/1024 size_MB from v$log;

select group#,member from v$logfile order by group#; select group#, thread#, status, bytes/1024/1024 size_MB from v$standby_log; --show parameter db_create_online_log_dest --alter system switch all logfile; --alter database add logfile thread &thread group &group size &size; --alter database drop logfile group &group; --alter database drop logfile member '&member'; Sample Output:     GROUP#    THREAD#    MEMBERS STATUS      SIZE_MB ---------- ---------- ---------- ---------- --------          1          1          2 INACTIVE      4,096          2          1          2 INACTIVE      4,096          3          1          2 CURRENT       4,096          6          2          2 INACTIVE      4,096          7          2          2 CURRENT       4,096          8          2          2 INACTIVE      4,096     GROUP# MEMBER ---------- ------------------------------------------------------------          1 +MY_DATA/mydb/onlinelog/group_1.324.807662009          1 +MY_FRA/mydb/onlinelog/group_1.2611.807662031          2 +MY_DATA/mydb/onlinelog/group_2.336.807662061          2 +MY_FRA/mydb/onlinelog/group_2.2601.807662085          3 +MY_DATA/mydb/onlinelog/group_3.321.807662115          3 +MY_FRA/mydb/onlinelog/group_3.2607.807662137 ...snip...