Here is a small script adopted from the book "RMAN Recipes for Oracle 11g" by Darl Kuhn, Sam R. Alapati and Arup Nanda. This script when run in from SQL Plus, will display the amount of used space in the current database flash recovery area.
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 999999.99; COLUMN RECLAIMABLE_MB HEADING 'Reclaimable Mbytes'; COLUMN RECLAIMABLE_MB FORMAT 9999999.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;
Hre is a sample output run against a newly created 11g database
Flash Recovery Area Usage File Type Used MBytes Reclaimable Mbytes Number of files -------------------- ----------- ------------------ --------------- CONTROL FILE 716.17 .00 1 REDO LOG 11649.70 .00 3 ARCHIVED LOG 31957.11 391.11 10 BACKUP PIECE 79272.10 .00 2 IMAGE COPY .00 .00 0 FLASHBACK LOG .00 .00 0 FOREIGN ARCHIVED LOG .00 .00 0 ----------- ------------------ Totals: 123595.09 391.11
The concept is very simple. Oracle provides two views for retrieving information about the flash recovery area. One is the v$recovery_file_dest
that contains information about the flash recovery area associated with the current database. A select * on this view will give us an output like the following :
SQL> select * from v$recovery_file_dest; NAME -------------------------------------------------------------------------------- SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- --------------- /u01/app/oracle/flash_recovery_area 2147483648 1668797952 93845504 16
This view provides the total number of used and reclaimable bytes from the flash recovery area. The next view named v$flash_recovery_area_usage
, provided percentage information about each type of file that can be stored in the flash recovery area. Doing a select * on this view gives up the following results.
SQL> set linesize 100; SQL> select * from v$flash_recovery_area_usage FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE ,45 0 1 REDO LOG 7,32 0 3 ARCHIVED LOG 20,08 4,37 10 BACKUP PIECE 49,81 0 2 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0
... so the next logical thing in order to get something meaningful out of these is to join them and this is what the initial script does. Since the v$recovery_file_dest
view has only one column, all columns of the v$flash_recovery_area_usage
view refer this hence the no need for a where clause in the script's sql.