oracle archive log history

By Ravi Karamsetty | October 22, 2015 |

Here is the oracle sql script that comes handy to find out number of archive logs that are generated by database by day by hour. set lines 120 set pages 999 spool log_history.log SELECT to_char(first_time,’YYYY-MON-DD’) day, to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”, to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”, to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”, to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “03”, to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “04”, to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “05”, to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “06”, to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “07”, to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “08”, to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) … Continue reading “oracle archive log history”

Read More