@@header

set term off
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display houly and daily Flashbacklog switches by size
*  Parameters : None
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  06-Jan-17  Vishal Gupta  Created
* 
*/
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=KB
--DEFINE size_divider="1024"
DEFINE size_label=MB
DEFINE size_divider="1024/1024"
--DEFINE size_label=GB
--DEFINE size_divider="1024/1024/1024"


PROMPT
PROMPT ************************************************************************
PROMPT *   Flashback Database Stats
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 begin_time                 HEADING "Begin Time|DD-Mon-YY HH"                  FORMAT A15
COLUMN "Total"                    HEADING "Day|Total|(&size_label)"                  FORMAT 9999
COLUMN "Day"                      HEADING "Day"                                      FORMAT A3
COLUMN db_data                    HEADING "DB|Data|(&&size_label)"                   FORMAT 999,999
COLUMN redo_data                  HEADING "Redo|Data|(&&size_label)"                 FORMAT 999,999
COLUMN flashback_data             HEADING "FlashBack|Data|(&&size_label)"            FORMAT 999,999
COLUMN estimated_flashback_size   HEADING "Estimated|FlashBack|Size|(&&size_label)"  FORMAT 999,999,999



SELECT  to_char(trunc(f.begin_time,'HH24'),'DD-Mon-YY HH24')      begin_time
	  ,  '|'                                                      separator
      , to_char(trunc(f.begin_time,'HH24'), 'Dy')                 "Day"
	  ,  '|'                                                      separator
	  , ROUND(SUM(((f.db_data)/&size_divider)))                   db_data
	  , ROUND(SUM(((f.redo_data)/&size_divider)))                 redo_data
	  , ROUND(SUM(((f.flashback_data)/&size_divider)))            flashback_data
	  , ROUND(SUM(((f.estimated_flashback_size)/&size_divider)))  estimated_flashback_size
 from  gv$flashback_database_stat f  
 where 1=1
   AND f.begin_time > TRUNC(sysdate) - &days
 group by trunc(f.begin_time,'HH24')
 order by trunc(f.begin_time,'HH24')
/

@@footer