Oracle Technology‎ > ‎Database‎ > ‎database notes‎ > ‎

Log Switch History

posted 20 May 2014, 08:14 by Alan Nolan-Davies
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')
Comments