@@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=GB DEFINE size_divider="1024/1024/1024" DEFINE round_precision=2 PROMPT PROMPT ************************************************************************ PROMPT * Flashback Log Generation (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(f.first_time),'DD-Mon-YY') "Date", to_char(f.first_time, 'Dy') "Day", '|' separator, ROUND(SUM(((f.bytes)/&size_divider))) "Total", '|' separator, ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'00',(f.bytes)/&size_divider,0))) "h0", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'01',(f.bytes)/&size_divider,0))) "h1", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'02',(f.bytes)/&size_divider,0))) "h2", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'03',(f.bytes)/&size_divider,0))) "h3", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'04',(f.bytes)/&size_divider,0))) "h4", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'05',(f.bytes)/&size_divider,0))) "h5", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'06',(f.bytes)/&size_divider,0))) "h6", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'07',(f.bytes)/&size_divider,0))) "h7", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'08',(f.bytes)/&size_divider,0))) "h8", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'09',(f.bytes)/&size_divider,0))) "h9", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'10',(f.bytes)/&size_divider,0))) "h10", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'11',(f.bytes)/&size_divider,0))) "h11", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'12',(f.bytes)/&size_divider,0))) "h12", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'13',(f.bytes)/&size_divider,0))) "h13", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'14',(f.bytes)/&size_divider,0))) "h14", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'15',(f.bytes)/&size_divider,0))) "h15", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'16',(f.bytes)/&size_divider,0))) "h16", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'17',(f.bytes)/&size_divider,0))) "h17", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'18',(f.bytes)/&size_divider,0))) "h18", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'19',(f.bytes)/&size_divider,0))) "h19", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'20',(f.bytes)/&size_divider,0))) "h20", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'21',(f.bytes)/&size_divider,0))) "h21", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'22',(f.bytes)/&size_divider,0))) "h22", ROUND(SUM(decode(to_char(f.first_time, 'hh24'),'23',(f.bytes)/&size_divider,0))) "h23" from v$flashback_database_logfile f where 1=1 AND f.first_time > sysdate - &days group by trunc(f.first_time), to_char(f.first_time, 'Dy') order by trunc(f.first_time) / @@footer