135 lines
7.0 KiB
SQL
135 lines
7.0 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display houly and daily redolog switches by size
|
|
* 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 and parameterized size granuality
|
|
* 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
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE size_label=GB
|
|
DEFINE size_divider="1024/1024/1024"
|
|
DEFINE round_precision=2
|
|
|
|
|
|
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 Size)
|
|
PROMPT * (Hourly and Daily figures in &&size_label)
|
|
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 (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB)
|
|
--------- --- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
|
|
*/
|
|
|
|
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 "Day|Total|(&size_label)" FORMAT 9999
|
|
COLUMN "Day" HEADING "Day" FORMAT A3
|
|
COLUMN h0 HEADING "h0|(&size_label)" FORMAT 999
|
|
COLUMN h1 HEADING "h1|(&size_label)" FORMAT 999
|
|
COLUMN h2 HEADING "h2|(&size_label)" FORMAT 999
|
|
COLUMN h3 HEADING "h3|(&size_label)" FORMAT 999
|
|
COLUMN h4 HEADING "h4|(&size_label)" FORMAT 999
|
|
COLUMN h5 HEADING "h5|(&size_label)" FORMAT 999
|
|
COLUMN h6 HEADING "h6|(&size_label)" FORMAT 999
|
|
COLUMN h7 HEADING "h7|(&size_label)" FORMAT 999
|
|
COLUMN h8 HEADING "h8|(&size_label)" FORMAT 999
|
|
COLUMN h9 HEADING "h9|(&size_label)" FORMAT 999
|
|
COLUMN h10 HEADING "h10|(&size_label)" FORMAT 999
|
|
COLUMN h11 HEADING "h11|(&size_label)" FORMAT 999
|
|
COLUMN h12 HEADING "h12|(&size_label)" FORMAT 999
|
|
COLUMN h13 HEADING "h13|(&size_label)" FORMAT 999
|
|
COLUMN h14 HEADING "h14|(&size_label)" FORMAT 999
|
|
COLUMN h15 HEADING "h15|(&size_label)" FORMAT 999
|
|
COLUMN h16 HEADING "h16|(&size_label)" FORMAT 999
|
|
COLUMN h17 HEADING "h17|(&size_label)" FORMAT 999
|
|
COLUMN h18 HEADING "h18|(&size_label)" FORMAT 999
|
|
COLUMN h19 HEADING "h19|(&size_label)" FORMAT 999
|
|
COLUMN h20 HEADING "h20|(&size_label)" FORMAT 999
|
|
COLUMN h21 HEADING "h21|(&size_label)" FORMAT 999
|
|
COLUMN h22 HEADING "h22|(&size_label)" FORMAT 999
|
|
COLUMN h23 HEADING "h23|(&size_label)" FORMAT 999
|
|
|
|
|
|
|
|
|
|
SELECT to_char(trunc(COMPLETION_TIME),'DD-Mon-YY') "Date",
|
|
to_char(COMPLETION_TIME, 'Dy') "Day",
|
|
'|' separator,
|
|
ROUND(SUM(((BLOCKS * BLOCK_SIZE)/&size_divider))) "Total",
|
|
'|' separator,
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'00',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h0",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'01',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h1",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'02',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h2",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'03',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h3",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'04',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h4",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'05',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h5",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'06',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h6",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'07',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h7",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'08',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h8",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'09',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h9",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'10',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h10",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'11',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h11",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'12',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h12",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'13',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h13",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'14',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h14",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'15',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h15",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'16',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h16",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'17',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h17",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'18',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h18",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'19',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h19",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'20',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h20",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'21',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h21",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'22',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h22",
|
|
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'23',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h23"
|
|
from v$archived_log
|
|
where standby_dest = 'NO'
|
|
AND CREATOR IN ('ARCH' , 'FGRD','LGWR','RFS')
|
|
AND completion_time > sysdate - &days
|
|
group by trunc(COMPLETION_TIME), to_char(COMPLETION_TIME, 'Dy')
|
|
order by trunc(COMPLETION_TIME)
|
|
/
|
|
|
|
@@footer
|