Query for Redo log Files and finding various details

Query for Redo log Files and finding various details

Script to find redo log file members.

select GROUP#,TYPE,MEMBER from v$logfile;

Script to find redo log file size

select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024 as "Size in MB",MEMBERS,STATUS from v$log;

Script to find both log file and its size

SELECT

    a.group#,

    a.thread#,

    a.sequence#,

    a.archived,

    a.status,

    b.member AS redolog_file_name,

    ( a.bytes / 1024 / 1024 ) AS "Size in MB"

FROM

    v$log a

    JOIN v$logfile b ON a.group# = b.group#

ORDER BY a.group#