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


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

  1. Hazratullah says:

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

    regards

  2. Najam Q says:

    Great script!

    Thanks,
    -q

  3. Luis F. Godoy says:

    Very helpful

    Thanks,

  4. Rafael Teodoro says:

    great!

  5. Shailesh Govani says:

    Very useful script

  6. Maulik says:

    Thanks :)

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>