Oracle 11g Redo Log size

Question

I want to know if there any query that shows the space reserved and available from redo logs groups?

Because I have only found this query

SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a

Answer 1

There are no such query to know know how much space is used in redo log files. At best, you can know how frequent they are being switched.

Online redo log files are used in circular manner. Once they use all the allocated space, they start overwriting from the beginning(after archiving the current redo log file if the database is in archive log mode).

Answer 2

The redo log buffer is flushed every 3 seconds, when 1/3 full, or when you commit. Oracle will write the buffer to the redo logs and they will switch when full. You will have to set your redo log size high enough so that you don't get switches too often. Getting a switch every few minutes on a busy production system is acceptable.

I find this query useful when looking at redo logs:

select a.group#, a.status, b.member 
from
v$log a, v$logfile b
where a.group#=b.group#

Good luck!

Answer 3

from here: https://oraclespin.com/2008/03/20/how-full-is-the-current-redo-log-file/

SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;

Good luck!