Skip to content
Oracle DBA Scripts & Database Utilities

Flash Recovery Area Space Usage (flash_space.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a comprehensive view of Flash Recovery Area (FRA) space usage by:

  • Showing the FRA location and size limit
  • Breaking down space usage by file type
  • Calculating reclaimable space (files that can be deleted if needed)
  • Showing actual space used (excluding reclaimable)

The Script

Section titled “The Script”
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
@flash_space.sql


-- No parameters required

Required Privileges

Section titled “Required Privileges”
SELECT ON V$RECOVERY_FILE_DEST
SELECT ON V$FLASH_RECOVERY_AREA_USAGE

Sample Output

Section titled “Sample Output”
NAME                SPACE_LIMIT FILE_TYPE            NUMBER_OF_FILES TOTAL_SPACE_USED TOTAL_PERCENT_SPACE_USED SPACE_RECLAIMABLE PERCENT_SPACE_RECLAIMABLE SPACE_USED PERCENT_SPACE_USED
------------------- ----------- -------------------- --------------- ---------------- ------------------------ ----------------- -------------------------- ---------- ------------------
+FRA                      40960 CONTROL FILE                       1              205                      .5                 0                          0        205                .5
+FRA                      40960 REDO LOG                           3             1536                     3.75                 0                          0       1536              3.75
+FRA                      40960 ARCHIVED LOG                      45            12800                    31.25             12000                      29.3        800              1.95
+FRA                      40960 BACKUP PIECE                      12            15360                    37.5              8192                        20       7168              17.5
+FRA                      40960 IMAGE COPY                         0                0                        0                 0                          0          0                 0
+FRA                      40960 FLASHBACK LOG                      4             4096                       10              2048                         5       2048                 5

Key Output Columns

Section titled “Key Output Columns”
  • NAME - FRA location (disk group or file system path)
  • SPACE_LIMIT - Total FRA size limit in MB
  • FILE_TYPE - Type of files (CONTROL FILE, ARCHIVED LOG, BACKUP PIECE, etc.)
  • NUMBER_OF_FILES - Count of files of this type
  • TOTAL_SPACE_USED - Total space used by this file type in MB
  • TOTAL_PERCENT_SPACE_USED - Percentage of FRA used by this file type
  • SPACE_RECLAIMABLE - Space that can be automatically freed if needed
  • PERCENT_SPACE_RECLAIMABLE - Percentage that is reclaimable
  • SPACE_USED - Actual space used (total minus reclaimable)
  • PERCENT_SPACE_USED - Actual percentage used

Common Use Cases

Section titled “Common Use Cases”

Monitor FRA Space

-- Run regularly to prevent FRA from filling up
@flash_space.sql
-- If TOTAL_PERCENT_SPACE_USED approaches 100%, take action

Identify Space Consumers

-- Look for file types using the most space
@flash_space.sql
-- Focus on ARCHIVED LOG and BACKUP PIECE rows

Troubleshooting

Section titled “Troubleshooting”

FRA Nearly Full

  • Check SPACE_RECLAIMABLE - Oracle can automatically delete these files
  • If SPACE_USED is high, manually delete old backups or archive logs
  • Consider increasing db_recovery_file_dest_size

No Output

  • Verify FRA is configured: show parameter db_recovery_file_dest
  • Check if using FRA: show parameter db_recovery_file_dest_size
Section titled “Related Scripts”