关于log的一些脚本

查询日志切换的频率(最近7天)RAC 和非RAC通用

select a.thread#,b.recid,to_char(b.first_time,'yyyy-mm-dd hh:mi:ss') start_time,a.recid,

to_char(a.first_time,'yyyy-mm-dd hh:mi:ss') end_time,round(((a.first_time-b.first_time)*25)*60,2) minutes from 

v$log_history a,v$log_history b where a.recid=b.recid+1 and sysdate-a.first_time<8 and a.thread#=b.thread# 

order by a.thread#, a.first_time ;

查询最近7天日志文件的度量

 

select b.destination "Destination", trunc(completion_time) completion_time,sum(blocks*block_size/1024/1024) "Total_Size(Gb)" from v$archived_log a,v$archive_dest b
where a.dest_id=b.dest_id and sysdate-completion_time<8 group by trunc(completion_time),b.destination;

查询未被删除的归档日志总量

select sum(blocks*block_size/1024/1024/1024)"Total_Size(Gb)" from v$archived_log where deleted='NO';

查询实例启动以来生成的总的归档日志总量

select sum(blocks*block_size/1024/1024/1024)  "Total_Size(Gb)" from v$archived_log where deleted='YES';

查询每个归档目的地生成的归档日志总量

select dest_id,sum(blocks*block_size/1024/1024/1024)"Total_Size(Gb)" from v$archived_log where deleted='NO' group by dest_id;