@@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
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