The Oracle (tm) Users' Co-Operative FAQ

How much redo is filled in my redo logs?


Author's name: K Gopalakrishnan

Author's Email: kaygopal@yahoo.com

Date written: November 21, 2001

Oracle version(s): Oracle 8.0.5 and above

There are no V$ views which exposes this information. So we directly query X$tables and you need to run this script connected as SYS or Internal. Alternatively if you use Oracle Statspack to monitor your database you can run replace X$ to X_$ wherever applicable and run as privileged user.

Back to index of questions


Finding how much percentage of current redo log is filled is bit tricky since the information is not exposed in any V$ views. We have to directly query the X$tables to get that information. The X$views we use here are x$kccle (Kernel Cache ControlfileComponent Log file Entry) and x$kcccp (Kernel Cache Checkpoint Progress).

 

select
        le.leseq                               current_log_sequence#,
        100 * cp.cpodr_bno / le.lesiz          percentage_full
from
        x$kcccp cp,
        x$kccle le
where
        le.leseq =cp.cpodr_seq
and     le.ledup != 0
;

CURRENT_LOG_SEQUENCE# PERCENTAGE_FULL
--------------------- ---------------
                    6      .428710938

Here x$kcccp.cpodr_bno tells the current log block in the redo log file and x$kccle.lesiz gives the number of (log) blocks in that log file.


Further reading: None


Back to top

Back to index of questions