Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘how to check how many archives generated per hour’

Script to find archivelog generation per hour

Posted by Pavan DBA on February 7, 2012


set pagesize 120; 
set linesize 200; 
col day for a8; 
spool archivelog.lst

PROMPT Archive log distribution per hours on each day … 
  

select 
  to_char(first_time,’YY-MM-DD’) day, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’00’,1,0)),’999′) “00”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’01’,1,0)),’999′) “01”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’02’,1,0)),’999′) “02”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’03’,1,0)),’999′) “03”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’04’,1,0)),’999′) “04”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’05’,1,0)),’999′) “05”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’06’,1,0)),’999′) “06”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’07’,1,0)),’999′) “07”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’08’,1,0)),’999′) “08”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’09’,1,0)),’999′) “09”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’10’,1,0)),’999′) “10”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’11’,1,0)),’999′) “11”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’12’,1,0)),’999′) “12”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’13’,1,0)),’999′) “13”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’14’,1,0)),’999′) “14”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’15’,1,0)),’999′) “15”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’16’,1,0)),’999′) “16”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’17’,1,0)),’999′) “17”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’18’,1,0)),’999′) “18”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’19’,1,0)),’999′) “19”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’20’,1,0)),’999′) “20”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’21’,1,0)),’999′) “21”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’22’,1,0)),’999′) “22”, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’23’,1,0)),’999′) “23”, 
  COUNT(*) TOT 
from v$log_history 
group by to_char(first_time,’YY-MM-DD’) 
order by day ;

Posted in Administration, Scripts | Tagged: , , , , , | Leave a Comment »

 
%d bloggers like this: