Scripts:显示数据库每小时每日redo切换频率的脚本logswitchfreq.sql

@@header


set term off
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display houly and daily redolog switches by frequency
*  Parameters : None
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  18-Mar-14  Vishal Gupta  Added separator columns for daily total
*  27-Feb-12  Vishal Gupta  Formated the output
*  05-Aug-04  Vishal Gupta  First Draft
*
*/
set term on


SET head on FEED off ECHO OFF LINES 1000 TRIMSPOOL ON TRIM on PAGES 1000


COLUMN separator HEADING "!|!|!"                 FORMAT A1




COLUMN "Date"  HEADING "Date"      FORMAT A9
COLUMN "Total" HEADING "Total|(#)" FORMAT 9999
COLUMN "Day"   HEADING "Day"       FORMAT A3
COLUMN h0      HEADING "h0|(#)"    FORMAT 999
COLUMN h1      HEADING "h1|(#)"    FORMAT 999
COLUMN h2      HEADING "h2|(#)"    FORMAT 999
COLUMN h3      HEADING "h3|(#)"    FORMAT 999
COLUMN h4      HEADING "h4|(#)"    FORMAT 999
COLUMN h5      HEADING "h5|(#)"    FORMAT 999
COLUMN h6      HEADING "h6|(#)"    FORMAT 999
COLUMN h7      HEADING "h7|(#)"    FORMAT 999
COLUMN h8      HEADING "h8|(#)"    FORMAT 999
COLUMN h9      HEADING "h9|(#)"    FORMAT 999
COLUMN h10     HEADING "h10|(#)"   FORMAT 999
COLUMN h11     HEADING "h11|(#)"   FORMAT 999
COLUMN h12     HEADING "h12|(#)"   FORMAT 999
COLUMN h13     HEADING "h13|(#)"   FORMAT 999
COLUMN h14     HEADING "h14|(#)"   FORMAT 999
COLUMN h15     HEADING "h15|(#)"   FORMAT 999 
COLUMN h16     HEADING "h16|(#)"   FORMAT 999
COLUMN h17     HEADING "h17|(#)"   FORMAT 999
COLUMN h18     HEADING "h18|(#)"   FORMAT 999
COLUMN h19     HEADING "h19|(#)"   FORMAT 999
COLUMN h20     HEADING "h20|(#)"   FORMAT 999
COLUMN h21     HEADING "h21|(#)"   FORMAT 999
COLUMN h22     HEADING "h22|(#)"   FORMAT 999
COLUMN h23     HEADING "h23|(#)"   FORMAT 999


PROMPT
PROMPT *******************************************************************************************************************************************
PROMPT *   A R C H I V E    L O G    S W I T C H    S U M M A R Y (By Frequency)
PROMPT *   (Hourly and Daily figures in number of archivelogs)
PROMPT *******************************************************************************************************************************************
PROMPT


PROMPT -                     <-------------------------------------------------------- Hourly Total ----------------------------------------------->
/*
                       Total   h0   h1   h2   h3   h4   h5   h6   h7   h8   h9  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23
         Date      Day   (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)
         --------- --- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
*/


SELECT  to_char(trunc(first_time),'DD-Mon-YY') "Date",
        to_char(first_time, 'Dy') "Day",
         '|'                                               separator,
        count(1) Total,
         '|'                                               separator,
        SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
 from v$log_history
-- where standby_dest = 'NO'
-- and   CREATOR IN ('ARCH' , 'FGRD')
group by trunc(first_time), to_char(first_time, 'Dy')
order by trunc(first_time)
/


@@footer