129 lines
5.4 KiB
SQL
129 lines
5.4 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display houly and daily redolog switches by frequency
|
|
* Parameters : None
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 30-Dec-14 Vishal Gupta Added number of days as input parameter
|
|
* 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
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE days
|
|
DEFINE days="&&1"
|
|
|
|
set term off
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&days','','90','&&days')) "_days"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
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 * Input Parameters
|
|
PROMPT * - Days = '&&days'
|
|
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 (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#)
|
|
--------- --- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
|
|
*/
|
|
|
|
|
|
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
|
|
|
|
|
|
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 1=1
|
|
-- and standby_dest = 'NO'
|
|
-- and CREATOR IN ('ARCH' , 'FGRD')
|
|
and first_time > sysdate - &&days
|
|
group by trunc(first_time), to_char(first_time, 'Dy')
|
|
order by trunc(first_time)
|
|
/
|
|
|
|
@@footer
|