Log Switch History Matrix

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
 * The matrix of log switch history

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