Flash Recovery Area Usage

SET PAGESIZE 66;
SET LINESIZE 80;

REPHEADER PAGE CENTER 'Flash Recovery Area Usage';

COLUMN FILE_TYPE FORMAT a20;
COLUMN FILE_TYPE HEADING 'File Type';

COLUMN USED_MB HEADING 'Used MBytes';
COLUMN USED_MB FORMAT 9999999999.99;
COLUMN RECLAIMABLE_MB HEADING 'Reclaimable Mbytes';
COLUMN RECLAIMABLE_MB FORMAT 99999999999.99;

COLUMN NUMBER_OF_FILES HEADING 'Number of files';

BREAK ON REPORT
COMPUTE SUM LABEL 'Totals:' OF USED_MB RECLAIMABLE_MB ON REPORT;

SELECT
  rau.file_type,
  rfd.space_used * rau.percent_space_used / 1024 / 1024 as USED_MB,
  rfd.space_reclaimable * rau.percent_space_reclaimable / 1024 / 1024 as RECLAIMABLE_MB,
  rau.number_of_files as NUMBER_OF_FILES
FROM
  v$recovery_file_dest rfd, v$flash_recovery_area_usage rau;


     Flash Recovery Area Usage

File Type                      Used MBytes Reclaimable Mbytes Number of files
-------------------- ----------- ------------------ -------------------------- ------------------
CONTROL FILE              247.44       .00  1
REDO LOG                       .00        .00  0
ARCHIVED LOG               876.35        29.75   4
BACKUP PIECE               24640.90 .00  2
IMAGE COPY               .00         .00  0
FLASHBACK LOG       .00         .00  0
FOREIGN ARCHIVED LOG      .00          .00  0
    ----------- ----------------------------- ----------------------------- ----------
Totals:                           25764.69      29.75


SQL>  select name ,SPACE_LIMIT/1024/1024/1024  SPACE_LIMIT_GB , SPACE_USED/1024/1024/1024 SPACE_USED_GB , ceil((space_used/space_limit)*100) used_prc from  v$recovery_file_dest   ;

SPACE_LIMIT_GB SPACE_USED_GB   USED_PRC

-------------- ------------- ------------------------ ------------- ----------

           225    161.083057         72