查归档日志文件每小时生成量

在Oracle数据库中，通过v$archived_log数据字典视图查询该数据库的归档日志文件的生成情况。如果你以为在rac下需要查的gv$archvied_log视图，这其实是一个错误的想法。无论在单实例数据库，还是多实例的RAC数据库，都是查这个视图来获取信息。查当天每小时的归档日志生成量selectlogtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selecttrunc(first_time,'hh')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_ID=1anda.FIRST_TIMEtrunc(sysdate))groupbylogtimeorderbylogtimedesc;查最近一周每天的归档日志生成量selectlogtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selecttrunc(first_time,'dd')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_ID=1anda.FIRST_TIMEtrunc(sysdate-7))groupbylogtimeorderbylogtimedesc;如果你需要知道RAC下各个节点的归档日志情况，我将上面脚本略作修改，增加thread#列。查当天每小时的各个实例的归档日志生成量selectTHREAD#,logtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selecta.THREAD#,trunc(first_time,'hh')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_ID=1anda.FIRST_TIMEtrunc(sysdate))groupbyTHREAD#,logtimeorderbyTHREAD#,logtimedesc;查最近一周每天的各个实例的归档日志生成量selectTHREAD#,logtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selectTHREAD#,trunc(first_time,'dd')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_ID=1anda.FIRST_TIMEtrunc(sysdate-7))groupbyTHREAD#,logtimeorderbyTHREAD#,logtimedesc;