SQL script to check available space in your recoveryarea (db_recovery_file_dest_size)

We’re hitting a lot of ORA-19815, ORA-19809, ORA-00257, ORA-16038 these days on some of our development databases – see here.


Scroll down for an updated version. The space_reclaimable should be substracted from space_used.

Script below displays percentage used in in your recoveryarea(s) as defined in init parameter ‘db_recovery_file_dest_size’.

col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999

SELECT name
,   ceil( space_limit / 1024 / 1024) SIZE_M
,   ceil( space_used  / 1024 / 1024) USED_M
,   decode( nvl( space_used, 0),
    0, 0
    , ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/

kudos: http://www.shutdownabort.com/errors/ORA-19809.php

Update – SUBSTRACT replaimable space from space_used.

col name for a32
col size_m for 999,999,999
col reclaimable_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999

SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used  / 1024 / 1024) USED_M
, ceil( space_reclaimable  / 1024 / 1024) RECLAIMABLE_M
, decode( nvl( space_used, 0),
 0, 0
 , ceil ( ( ( space_used - space_reclaimable ) / space_limit) * 100) ) PCT_USED
 FROM v$recovery_file_dest
ORDER BY name
/
SQL> SELECT name
  2  , ceil( space_limit / 1024 / 1024) SIZE_M
  3  , ceil( space_used  / 1024 / 1024) USED_M
  4  , ceil( space_reclaimable  / 1024 / 1024) RECLAIMABLE_M
  5  , decode( nvl( space_used, 0),
  6   0, 0
  7   , ceil ( ( ( space_used - space_reclaimable ) / space_limit) * 100) ) PCT_USED
  8   FROM v$recovery_file_dest
  9  ORDER BY name
 10  /

NAME                                 SIZE [M]     USED [M] RECLAIMABLE_M PCT_USED
-------------------------------- ------------ ------------ ------------- --------
/recoveryarea                          14,336        6,338         6,337        1

9 thoughts on “SQL script to check available space in your recoveryarea (db_recovery_file_dest_size)

  1. hello!
    hope all are fine.
    the scripts was very good and helped view the database recovery area used as a user friendly display!

    regards

Leave a Reply

Your email address will not be published. Required fields are marked *