Databases Reference
In-Depth Information
Solution
The data dictionary view V$RECOVERY_FILE_DEST shows the sum of various types of files in the fast recovery area in
terms of percentages of the total space. It has only one row. Here is an example of how you can use the view:
SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
--------------- ----------- ---------- ----------------- --------------- ------
+FRA 1.0737E+10 2962227200 18874368 9 0
To see space used by different types of files in the fast recovery area, you should check the view
V$FLASH_RECOVERY_AREA_USAGE. Here is an example of how you can see the contents of the fast recovery area:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE .18 0 1 0
REDO LOG 1.49 0 3 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 24.91 .18 3 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 1 0 2 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
Note the number of files of each category. The sum of the total number of files (1+3+3+2) equals 9, as shown in
the previous example querying V$RECOVERY_FILE_DEST.
V$FLASH_RECOVERY_AREA_USAGE shows the percentages of the total space consumed, not the space itself.
You may want to join it to V$RECOVERY_FILE_DEST to see the total space occupied by each type of file,
as shown here:
select
file_type,
space_used*percent_space_used/100/1024/1024 used,
space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable,
frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;
FILE_TYPE USED RECLAIMABLE NUMBER_OF_FILES
----------------------- ---------- ----------- ---------------
CONTROL FILE 13.5054 0 1
REDO LOG 111.7947 0 3
ARCHIVED LOG 1410.564 .1464 79
BACKUP PIECE 3698.979 .2135 6
IMAGE COPY 0 0 0
FLASHBACK LOG 261.8547 0 7
FOREIGN ARCHIVED LOG 0 0 0
AUXILIARY DATAFILE COPY 0 0 0
