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. |
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).
selectle.leseq current_log_sequence#,
100 * cp.cpodr_bno / le.lesiz percentage_full
fromx$kcccp cp,
x$kccle le
wherele.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
