/*
*
*  Author        : Vishal Gupta
*  Purpose       : Display Recovery Area Usage
*  Compatibility : 11.2 and above
*  Parameters    : NONE
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  22-Jan-16  Vishal Gupta  Added total computation
*  02-May-13  Vishal Gupta  Updated to display space used in GB
*  30-Jul-12  Vishal Gupta  Created
*  
*/

PROMPT *****************************************************************
PROMPT *  R E C O V E R Y     A R E A    U S A G E
PROMPT *****************************************************************



COLUMN inst_id                 HEADING "I#"                    FORMAT 99
COLUMN name                    HEADING "Name"                  FORMAT a28
COLUMN value                   HEADING "Value"                 FORMAT a15
COLUMN type                    HEADING "Type"                  FORMAT a10
COLUMN ordinal                 HEADING "Ordinal"               FORMAT 99
COLUMN isdefault               HEADING "Def|ault?"             FORMAT a5
COLUMN ismodified              HEADING "Modi|fied?"            FORMAT a8
COLUMN isadjusted              HEADING "Adjus|ted?"            FORMAT a5

SELECT p.inst_id
     , p.name
     , TRIM(TO_CHAR(ROUND(p.value / 1024 / 1024 / 1024),'999,999') || ' GB') value
  FROM gv$system_parameter p
 WHERE p.name = 'db_recovery_file_dest_size'  
ORDER BY p.inst_id
;

COLUMN file_type                   HEADING "File Type"               FORMAT a25
COLUMN number_of_files             HEADING "Files (#)"               FORMAT 999,999,999
COLUMN space_used                  HEADING "Space|Used|(MB)"         FORMAT 999,999,999
COLUMN space_reclaimable           HEADING "Space|Reclaimable|(MB)"  FORMAT 999,999,999
COLUMN percent_space_used          HEADING "Space|Used|(%)"          FORMAT 999.99
COLUMN percent_space_reclaimable   HEADING "Space|Reclaimable|(%)"   FORMAT 999.99

BREAK ON REPORT    

COMPUTE SUM LABEL 'Total' OF number_of_files            FORMAT 999,999,999  ON REPORT 
COMPUTE SUM LABEL 'Total' OF space_used                 FORMAT 999,999,999  ON REPORT 
COMPUTE SUM LABEL 'Total' OF space_reclaimable          FORMAT 999,999,999  ON REPORT 
COMPUTE SUM LABEL 'Total' OF percent_space_used         FORMAT 999.99  ON REPORT 
COMPUTE SUM LABEL 'Total' OF percent_space_reclaimable  FORMAT 999.99  ON REPORT 

SELECT r.file_type
     , r.number_of_files
     , ROUND(r.percent_space_used * p.value / 100 / 1024 / 1024)        space_used
     , ROUND(r.percent_space_reclaimable * p.value / 100 / 1024 / 1024) space_reclaimable
     , r.percent_space_used
     , r.percent_space_reclaimable
  FROM v$recovery_area_usage r
     , v$system_parameter p
 WHERE p.name = 'db_recovery_file_dest_size'       
;