• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Identify Different File types and space used in Flash Recovery Area

by admin

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

Filed Under: oracle, Oracle 10g, Oracle 11g, oracle 12c

Some more articles you might also be interested in …

  1. What is SQL Server Operating System ( SQLOS)
  2. How to check which options or features are used in the Oracle database
  3. How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
  4. Beginners Guide to Oracle Temporary Tablespace Groups
  5. Using CASE statements and CASE expressions in PL/SQL
  6. How to Recover From Lost or Missing Database Parameter Files (PFILE or SPFILE)
  7. Oracle RMAN: Monitoring Recovery Manager Jobs
  8. How to create restore points for PDB and perform flashback at PDB level
  9. Oracle Database 12c New Feature: SYSRAC administrative privilege
  10. Interview Questions : Oracle 12c Multitenant Database Architecture

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright