Log Switch History

Post date: May 20, 2014 3:14:14 PM

SELECT   TO_CHAR(first_time, 'YYYY-MM-DD') AS "Date", TO_CHAR(first_time, 'Day') AS "Day",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) AS "00",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) AS "01",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) AS "02",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) AS "03",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) AS "04",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) AS "05",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) AS "06",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) AS "07",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) AS "08",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) AS "09",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) AS "10",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) AS "11",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) AS "12",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) AS "13",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) AS "14",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) AS "15",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) AS "16",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) AS "17",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) AS "18",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) AS "19",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) AS "20",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) AS "21",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) AS "22",

         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) AS "23"

FROM     v$log_history

GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD'), TO_CHAR(first_time, 'Day') 

ORDER BY TO_CHAR(first_time, 'YYYY-MM-DD'), TO_CHAR(first_time, 'Day')