@@header
set term off
/*
*
*  Author  : Vishal Gupta
*  Purpose : Display RMAN backup size from catalog
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  23-Jun-08  Vishal Gupta  First Draft
*/
set term on

TTITLE LEFT "R M A N    B A C K U P    S I Z E S"  
PROMPT
COLUMN input_mb_sum  FORMAT 9,999,999.99 HEADING "Input|(MB)"
COLUMN output_mb_sum FORMAT 9,999,999.99 HEADING "Output|(MB)"
COLUMN percent_compress FORMAT 990.99 HEADING "Compression (%)"
SELECT s.db_name
     , s.output_device_type
     , ROUND(sum(s.input_bytes)/1024/1024,2) input_mb_sum
     , ROUND(sum(s.output_bytes)/1024/1024,2) output_mb_sum
     , ROUND( (sum(s.input_bytes) - sum(s.output_bytes)) * 100 / sum(s.input_bytes),2)  percent_compress
  FROM rc_rman_status s
 WHERE s.END_TIME > sysdate - 1
   AND s.output_device_type IS NOT NULL -- ignore connection entries
 GROUP by db_name
        , s.output_device_type
ORDER BY db_name
        , s.output_device_type
;

@@footer