x$kccle---- [K]ernel [C]ache [C]ontrolfile management [L]ogfil[E] record
SQL> DESC X$KCCLE
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
LENUM NUMBER
LESIZ NUMBER -----log file size(以操作系统块大小表示)
LESEQ NUMBER ------log file 的sequence#,它等于v$log.sequence#
LEHWS NUMBER
LEBSZ NUMBER ------log file的块大小，它也等于操作系统块大小
LENAB NUMBER
LEFLG NUMBER
LETHR NUMBER
LELFF NUMBER
LELFB NUMBER
LELOS VARCHAR2(16)
LELOT VARCHAR2(20)
LENXS VARCHAR2(16)
LENXT VARCHAR2(20)
LEPVS VARCHAR2(16)
LEARF NUMBER
LEARB NUMBER
LEFNH NUMBER
LEFNT NUMBER
LEDUP NUMBER
查询当前操作系统的块大小：
SQL> select max(lebsz) from x$kccle;
MAX(LEBSZ)
----------
512
大多数平台的操作系统块都为512byte
SQL> select leseq log_file_sequence,lesiz*512/1024/1024||'Mb' log_file_size,lebsz||'byte' os_block_size from x$kccle where lesiz>0;
LOG_FILE_SEQUENCE LOG_FILE_SIZE OS_BLOCK_SIZE
----------------- -------------------- --------------------
78 150Mb 512byte
77 150Mb 512byte
SQL> select sequence#,bytes/1024/1024 log_file_size from v$log;
SEQUENCE# LOG_FILE_SIZE
---------- --------------------
78 150
77 150
查询当前日志文件使用率
SQL> select le.leseq current_sequence,round(100*cp.cpodr_bno/le.lesiz,2)||'%' percentage,le.lesiz*512/1024/1024||'Mb' log_file_size,
2 round((le.lesiz*512/1024/1024)*(cp.cpodr_bno/le.lesiz),2)||'Mb' used_size
3 from x$kcccp cp,x$kccle le where le.leseq =cp.cpodr_seq and le.lesiz>0;
CURRENT_SEQUENCE PERCENTAGE LOG_FILE_SIZE USED_SIZE
-------------------------- ----------------------------------------- -------------------- ------------------------------------------
78 3.86% 150Mb 5.78Mb
其中X$kcccp.cpodr_seq就是current logfile的sequence,具体可以看上一篇blog，x$kcccp.cpodr_bno为已写入日志文件最后一个RBA中的日志块数，所以用x$kcccp.cpodr_bno/x$kccle.lesiz就能得到当前日志文件的使用率