Question: How to find out the existence of different types of files and backups available with the usage of space in the Flash Recovery Area?
Starting with Oracle 10G Release 2, a new view has been made available: V$FLASH_RECOVERY_AREA_USAGE. V$FLASH_RECOVERY_AREA_USAGE shows the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.
SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- CONTROLFILE 0 0 0 ONLINELOG 0 0 0 ARCHIVELOG 39.86 39.86 24 BACKUPPIECE 59.19 28.93 4 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0
Percentage of space usable in the flash recovery area can be identified by:
SQL> SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable)FROM v$flash_recovery_area_usage;
(100-SUM(PERCENT_SPACE_USED))+SUM(PERCENT_SPACE_RECLAIMABLE)
------------------------------------------------------------
69.74
V$RECOVERY_FILE_DEST shows the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area:
SQL> select * from V$RECOVERY_FILE_DEST; NAME ---------------------------------------------------------------------- SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- --------------- [Flash recovery area (same as values of parameter db_recovery_file_dest)] 2147483648 2075840512 1477189632 27