set term off
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display hourly 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','','2','&&days'))   "_DAYS"
FROM DUAL;
set term on


/************************************
*  CONFIGURATION PARAMETERS
************************************/

DEFINE size_label=MB
DEFINE size_divider="1024/1024"
--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

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 A12
COLUMN "Day"   HEADING "Day"                                FORMAT A3
COLUMN "Total" HEADING "Hourly|Total|(&size_label)"         FORMAT 9,999,999
COLUMN "M00"   HEADING "Minutes|01-10 |(&size_label)  "     FORMAT 999,999
COLUMN "M10"   HEADING "Minutes|11-20 |(&size_label)  "     FORMAT 999,999
COLUMN "M20"   HEADING "Minutes|21-30 |(&size_label)  "     FORMAT 999,999
COLUMN "M30"   HEADING "Minutes|31-40 |(&size_label)  "     FORMAT 999,999
COLUMN "M40"   HEADING "Minutes|41-50 |(&size_label)  "     FORMAT 999,999
COLUMN "M50"   HEADING "Minutes|51-60 |(&size_label)  "     FORMAT 999,999




SELECT   TO_CHAR(trunc(COMPLETION_TIME,'HH24'),'DD-MON-YY HH24') "Date",
         to_char(COMPLETION_TIME, 'Dy') "Day",
         '|'                                               separator,
         ROUND(SUM(((BLOCKS * BLOCK_SIZE)/&&size_divider))) "Total",
         '|'                                               separator,
         ROUND(SUM(CASE WHEN TO_NUMBER(to_char(COMPLETION_TIME, 'MI')) BETWEEN 01 AND 10 THEN ROUND((BLOCKS * BLOCK_SIZE)/&&size_divider) ELSE 0 END)) "M00",
         ROUND(SUM(CASE WHEN TO_NUMBER(to_char(COMPLETION_TIME, 'MI')) BETWEEN 11 AND 20 THEN ROUND((BLOCKS * BLOCK_SIZE)/&&size_divider) ELSE 0 END)) "M10",
         ROUND(SUM(CASE WHEN TO_NUMBER(to_char(COMPLETION_TIME, 'MI')) BETWEEN 21 AND 30 THEN ROUND((BLOCKS * BLOCK_SIZE)/&&size_divider) ELSE 0 END)) "M20",
         ROUND(SUM(CASE WHEN TO_NUMBER(to_char(COMPLETION_TIME, 'MI')) BETWEEN 31 AND 40 THEN ROUND((BLOCKS * BLOCK_SIZE)/&&size_divider) ELSE 0 END)) "M30",
         ROUND(SUM(CASE WHEN TO_NUMBER(to_char(COMPLETION_TIME, 'MI')) BETWEEN 41 AND 50 THEN ROUND((BLOCKS * BLOCK_SIZE)/&&size_divider) ELSE 0 END)) "M40",
         ROUND(SUM(CASE WHEN TO_NUMBER(to_char(COMPLETION_TIME, 'MI')) BETWEEN 51 AND 60 THEN ROUND((BLOCKS * BLOCK_SIZE)/&&size_divider) ELSE 0 END)) "M50",
         '|'                                               separator
  from v$archived_log 
 where standby_dest = 'NO'
   and CREATOR IN ('ARCH' , 'FGRD')
   AND completion_time > sysdate - &days
group by trunc(COMPLETION_TIME,'HH24'), to_char(COMPLETION_TIME, 'Dy')
order by trunc(COMPLETION_TIME,'HH24')
;