Log Switch History Matrix

• The matrix of log switch history

Reference: http://www.confio.com/English/Tips/Log_File_Switch_Completion.php

SET lines 120
col "DAY(YYYY-MM-DD)" format A13
col "00" format A3
col "01" format A3
col "02" format A3
col "03" format A3
col "04" format A3
col "05" format A3
col "06" format A3
col "07" format A3
col "08" format A3
col "09" format A3
col "10" format A3
col "11" format A3
col "12" format A3
col "13" format A3
col "14" format A3
col "15" format A3
col "16" format A3
col "17" format A3
col "18" format A3
col "19" format A3
col "20" format A3
col "21" format A3
col "22" format A3
col "23" format A3

SELECT to_char(first_time,'YYYY-MM-DD') "DAY(YYYY-MM-DD)",
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') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history group by to_char(first_time,'YYYY-MM-DD')
ORDER BY "DAY(YYYY-MM-DD)";

DAY(YYYY-MM-D 00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2011-01-08      0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
2011-01-09      0   0   0   0   0   0   0   0   4   5   0   0   0   0   0   3   4   0   1   7   3  11   1   1
2011-01-10      1   0   0   0   0   0   0   0   4   7  16   0   2   2   9  14   7   0   4   7   7  10   2  11
2011-01-11      7   0   4   0   1   8   9   7   9  12   6  21   3   7   8  15   0   6   9  10   9   0  13   8
2011-01-12      8   0   1   7  11  13   0   4   0  23   8  13   2   9  10  14   0   3   5  13   2   6   9  22
2011-01-13      0   8  10   4   8   1   2   0  14  17  10   0   0   7   8  15  12   0  16   8   9  16   6  14
2011-01-14     11   1   8  18   6   9   6  15   0   7  18  14   0   7  18  27   0   1  11   7  12   6   8  16
2011-01-15     10   9   8  19   6   9   6  10   9  18   6  12  12  11   6  13   6   9   9   0   6  15  16  12

See Also

• Calculate the size of archive log files each day