SQL脚本:监控当前重做日志文件使用情况

这个脚本可以用来分析当前重做日志文件(redo logfile)已被用到了什么位置(position)、还剩余多少空间和已使用的百分比:

set linesize 200 pagesize 1400;
select le.leseq "Current log sequence No",
       100 * cp.cpodr_bno / le.lesiz "Percent Full",
       (cpodr_bno - 1) * 512  "bytes used exclude header",
       le.lesiz * 512 - cpodr_bno * 512 "Left space",
       le.lesiz  *512       "logfile size"
  from x$kcccp cp, x$kccle le
 where LE.leseq = CP.cpodr_seq
   and bitand(le.leflg, 24) = 8;

Sample:

SQL> set linesize 200 pagesize 1400;
SQL> select le.leseq "Current log sequence No",
  2         100 * cp.cpodr_bno / le.lesiz "Percent Full",
  3         (cpodr_bno - 1) * 512  "bytes used exclude header",
  4         le.lesiz * 512 - cpodr_bno * 512 "Left space",
  5         le.lesiz  *512       "logfile size"
  6    from x$kcccp cp, x$kccle le
  7   where LE.leseq = CP.cpodr_seq
  8     and bitand(le.leflg, 24) = 8;

Current log sequence No Percent Full bytes used exclude header Left space logfile size
----------------------- ------------ ------------------------- ---------- ------------
                    189   90.7612305                  95169536    9687552    104857600

/*  如上结果显示当前重做日志号为189，使用量百分比是90.7%
    当前日志被使用到了95169536+512 bytes(重做日志文件头)的位置，
    还剩余9687552 bytes的空间，该重做日志的总大小为104857600=100MB
*/

© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Does Oracle Goldengate support Parallel DML?
2. REDO LOG CORRUPTION – DROPPING REDO LOGS NOT POSSIBLE – CLEAR LOGFILE
3. Data Block Cache Header Format Changes (Oracle8 Physical layout)
4. 关于Oracle中supplemental log的补充说明
5. logfile switch causes incremental checkpoint?