Skip to content
Oracle DBA Scripts & Database Utilities

Flash Recovery Area Space Usage (fra_space.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a detailed breakdown of Flash Recovery Area space usage by file type, showing both used and reclaimable space.

The Script

Section titled “The Script”
ttitle 'Flash Recovery Area Space Usage'
set linesize 200
break on name on space_limit on report
compute sum of space_used  on name
compute sum of percent_space_used on name
compute sum of number_of_files on name
compute sum of PERCENT_SPACE_RECLAIMABLE on name
compute sum of space_reclaimable on name
compute sum of total_percent_space_used on name
compute sum of total_space_used on name


col name format a30 heading 'RECOVERY AREA LOCATION'
col space_limit format 9,999,999 heading 'SPACE|LIMIT|(MB)'
col number_of_files format 99,999 heading 'FILE|COUNT'


col total_percent_space_used format 999.99 heading 'TOTAL|PCT|SPACE|USED'
col total_space_used format 9,999,999 heading 'TOTAL|SPACE|USED|(MB)'


col percent_space_used format 99.99 heading 'PCT|SPACE|USED'
col space_used format 9,999,999 heading 'SPACE|USED|(MB)'


col PERCENT_SPACE_RECLAIMABLE format 99.99 heading 'PCT|RCLMBL'
col SPACE_RECLAIMABLE format 999,999 heading 'SPACE|RCLMBL|(MB)'


select a.name,
       a.space_limit/(1024*1024) space_limit,
       b.file_type,
       b.number_of_files,
       ((b.PERCENT_SPACE_USED/100)*a.SPACE_LIMIT)/(1024*1024) TOTAL_SPACE_USED,
       b.PERCENT_SPACE_USED TOTAL_PERCENT_SPACE_USED,
       ((b.PERCENT_SPACE_RECLAIMABLE/100)*a.SPACE_LIMIT)/(1024*1024) space_reclaimable,
       b.PERCENT_SPACE_RECLAIMABLE,
       (((b.PERCENT_SPACE_USED-b.PERCENT_SPACE_RECLAIMABLE)/100)*a.SPACE_LIMIT)/(1024*1024) space_used,
       b.PERCENT_SPACE_USED-b.PERCENT_SPACE_RECLAIMABLE PERCENT_SPACE_USED
from V$RECOVERY_FILE_DEST a,
     V$FLASH_RECOVERY_AREA_USAGE b
order by a.name, a.space_limit, b.file_type
/

Usage

Section titled “Usage”
-- Basic usage
@fra_space.sql

Parameters

Section titled “Parameters”

The script prompts for:

  • None - this script requires no parameters## Required Privileges
SELECT ANY DICTIONARY
-- OR --
SELECT ON V$RECOVERY_FILE_DEST
SELECT ON V$FLASH_RECOVERY_AREA_USAGE

Sample Output

Section titled “Sample Output”
Flash Recovery Area Space Usage


                                    SPACE              FILE     TOTAL       TOTAL     SPACE     PCT     SPACE     PCT
RECOVERY AREA LOCATION              LIMIT      FILE_TYPE              COUNT     SPACE  PCT SPACE  RCLMBL  RCLMBL     USED  SPACE
------------------------------ ---------- ------------------------- ------ ---------- --------- ------- ------ --------- ------
+FRA                              100,000 CONTROL FILE                   2         19      0.02       0   0.00        19   0.02
                                          REDO LOG                       3        150      0.15       0   0.00       150   0.15
                                          ARCHIVED LOG                 245     35,840     35.84  15,360  15.36    20,480  20.48
                                          BACKUP PIECE                  89     42,000     42.00  21,000  21.00    21,000  21.00
                                          IMAGE COPY                     0          0      0.00       0   0.00         0   0.00
                                          FLASHBACK LOG                 10      8,192      8.19       0   0.00     8,192   8.19
                                          FOREIGN ARCHIVED LOG           0          0      0.00       0   0.00         0   0.00
                               ********** ************************- ------ ---------- --------- ------- ------ --------- ------
                               sum                                     349     86,201     86.20  36,360  36.36    49,841  49.84

Key Output Columns

Section titled “Key Output Columns”
  • RECOVERY AREA LOCATION - FRA location path
  • SPACE LIMIT (MB) - Total FRA size allocated
  • FILE_TYPE - Type of file (ARCHIVED LOG, BACKUP PIECE, etc.)
  • FILE COUNT - Number of files of each type
  • TOTAL SPACE USED (MB) - Space used by file type
  • TOTAL PCT SPACE - Percentage of FRA used
  • SPACE RCLMBL (MB) - Space that can be reclaimed
  • PCT RCLMBL - Percentage reclaimable

Common Use Cases

Section titled “Common Use Cases”

Monitor FRA Space

-- Check FRA utilization and plan cleanup
@fra_space.sql

Pre-Backup Check

-- Ensure sufficient FRA space before backup
@fra_space.sql
Section titled “Related Scripts”