Segue abaixo algumas querys para validar o consumo de FRA (Fast Recovery Area)
Verificando consumo
set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;
NAME Allocated Space(GB) Used Space(GB) SPACE_RECLAIMABLE (GB) Estimated Space (GB)
-------------------------------------------------- ------------------- -------------- ---------------------- --------------------
/u02/fra 150 8.06 0 4.26
Outra query que irá exibir por item
select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG .98 0 10 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG .28 0 4 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
Verificando consumo
show parameter db_re
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02/fra
db_recovery_file_dest_size big integer 150G
db_recycle_cache_size big integer 0