Author's name: K Gopalakrishnan
Author's Email: firstname.lastname@example.org
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).
100 * cp.cpodr_bno / le.lesiz percentage_full
and le.ledup != 0
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