oracle sql脚本输出日志,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

*/