Friday, April 22, 2011

How full is the current redo log file?

Here is a query that can tell us how full the current redo log file is. This is useful  when we  need to predict when the next log file will be archived out.

SQL> 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 ;

Sample Output :
Current log sequence No      Percent Full        Current Block No         Size of Log in Blocks
-----------------------                -------------           -----------------             ---------------------
                  7                       18.1982422               18635                        102400

Enjoy   J J J


Sandesh said...

Thank you. Very useful query espically when archive_lag_target is set to 0 on the source database

Parvinder said...

Thanks dear