Anzahl der Redo Log Switches einer Oracle-Datenbank

Sie möchten auswerten, wieiviele Log Switches in den letzten Betriebstagen angefallen sind? Das folgende Skript gibt Ihnen Auskunft, in dem es die Views v$log_history bzw. v$archived_log überprüft und die Log Switches über Tag und Uhrzeit kulminiert:

Oracle Database: Anzahl der Log Switches
SET lines 150
SET trimspool ON
SET trimout    ON
 
COL 00:00 FORMAT 99999
COL 01:00 FORMAT 99999
COL 02:00 FORMAT 99999
COL 03:00 FORMAT 99999
COL 04:00 FORMAT 99999
COL 05:00 FORMAT 99999
COL 06:00 FORMAT 99999
COL 07:00 FORMAT 99999
COL 08:00 FORMAT 99999
COL 09:00 FORMAT 99999
COL 10:00 FORMAT 99999
COL 11:00 FORMAT 99999
COL 12:00 FORMAT 99999
COL 13:00 FORMAT 99999
COL 14:00 FORMAT 99999
COL 15:00 FORMAT 99999
COL 16:00 FORMAT 99999
COL 17:00 FORMAT 99999
COL 18:00 FORMAT 99999
COL 19:00 FORMAT 99999
COL 20:00 FORMAT 99999
COL 21:00 FORMAT 99999
COL 22:00 FORMAT 99999
COL 23:00 FORMAT 99999
 
 
SELECT * FROM (
   SELECT * FROM (
      SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '00', 1, 0), '99'
              )
           ) "00:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '01', 1, 0), '99'
              )
           ) "01:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '02', 1, 0), '99'
              )
           ) "02:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '03', 1, 0), '99'
              )
           ) "03:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '04', 1, 0), '99'
              )
           ) "04:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '05', 1, 0), '99'
              )
           ) "05:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '06', 1, 0), '99'
              )
           ) "06:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '07', 1, 0), '99'
              )
           ) "07:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '08', 1, 0), '99'
              )
           ) "08:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '09', 1, 0), '99'
              )
           ) "09:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '10', 1, 0), '99'
              )
           ) "10:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '11', 1, 0), '99'
              )
           ) "11:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '12', 1, 0), '99'
              )
           ) "12:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '13', 1, 0), '99'
              )
           ) "13:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '14', 1, 0), '99'
              )
           ) "14:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '15', 1, 0), '99'
              )
           ) "15:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '16', 1, 0), '99'
              )
           ) "16:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '17', 1, 0), '99'
              )
           ) "17:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '18', 1, 0), '99'
              )
           ) "18:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '19', 1, 0), '99'
              )
           ) "19:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '20', 1, 0), '99'
              )
           ) "20:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '21', 1, 0), '99'
              )
           ) "21:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '22', 1, 0), '99'
              )
           ) "22:00"
      , SUM
          (TO_NUMBER
             (
              DECODE
                  (TO_CHAR(FIRST_TIME, 'HH24'), 
                  '23', 1, 0), '99'
              )
           ) "23:00"
       FROM V$LOG_HISTORY
       WHERE 
           extract(year FROM FIRST_TIME) = 
           extract(year FROM sysdate)
       GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
     ) ORDER BY 
           TO_DATE(extract(year FROM sysdate) ||
                        DAY, 'YYYY DD/MM') DESC
     ) WHERE ROWNUM <8;

 

Die Ausgabe kann beispielsweise wie folgt aussehen: 

Tag   00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 ...
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
02/02     4     1     1     0     1     1     0     1     2     1     3 ...
01/02     2     0     1     1     0     1     1     0     1     1     1 
31/01     3     1     1     1     0     1     0     1     1     1     2 
30/01     4     2     1     1     1     1     1     1     1     2     3  
29/01     0     0     0     0     0     0     0     0     0     7    16  
28/01     0     0     0     0     0     0     0     0     0     0     0