Problem solve Get help with specific problems with your technologies, process and projects.

Basic Oracle space monitoring scripts

To keep your Oracle databases running 24x7, you need some basic space monitoring scripts. Here are three useful ones.

In order to keep your Oracle databases running 24x7, you're going to need some basic monitoring scripts. Here are a few that I wrote for the HP platform.

There are two important parts of monitoring when it comes to Oracle databases: tablespaces and the HP-UX filesystems. If your shop isn't ready yet to make the investment some third-party software, you can create your own scripts in a couple hours.

When monitoring the database, the most important piece of information is the amount of free space in your tablespaces. So, here is some SQL that displays total, free, and used space in all tablespaces in your database:

select  tbs.tablespace_name, 
        tot.bytes/1024 total, 
        tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used, 
        sum(nvl(fre.bytes,0))/1024 free, 
        (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1  ;
This will flag the tablespaces that have less then 90% free space in them (with an asterisk in the last column).

Here is another useful SQL script that will tell you the minimum usable extents in a tablespace. This will identify when a table or index won't be able to make another extent in a tablespace. You don't want this number to reach zero -- that will cause errors in your application.

 col name noprint new_value dbname
  col value noprint new_value block_size
  select, value
  from v$database db, v$parameter pr
  where = 'db_block_size' ;

 sum(fre.bytes/1024) free_space,
 count(*) num_free,
 max(fre.bytes/1024) largest,
 /*avg(fre.bytes/1024) avg_size,*/
   nvl(mni.max_next_extent,&block_size))/1024 grt_extent,
  fre.bytes), fre.bytes,
   nvl(mni.max_next_extent,&block_size))),0)) min_usable
 dba_free_space fre,
 (select tab.tablespace_name, 
  max(tab.next_extent) max_next_extent
 from dba_tables tab
 group by tab.tablespace_name) mnt,
 (select idx.tablespace_name, 
  max(idx.next_extent) max_next_extent
 from dba_indexes idx
 group by idx.tablespace_name) mni
 fre.tablespace_name = mnt.tablespace_name(+) and
    fre.tablespace_name = mni.tablespace_name(+)
  group by
  order by 6 desc,1  ;

The output from both of these scripts can easily be parsed using a shell script, and scheduled using cron, in order to automate the monitoring of the Oracle database.

The last part of monitoring is at the filesystem level, most importantly, the archive log destination. Here is a simple script that monitors a filesystem called /arch and sends me an email when it reaches 90%:

  SPACE_USED=`df -Pk /arch | tail -1 | awk '{print $5}' | cut -d % -f 1`

  if [ $SPACE_USED -ge 90 ]
    echo Filesystem /arch is at $SPACE_USED percent | mail jimg

By using these three scripts, you should be able to prevent most space problems that affect Oracle databases.

Reader Feedback

Steven T. writes: The first script is another incarnation of the TS Pctfree report. I like the second script. If it were modified to use DBA_SEGMENTS instead of outer joining to DBA_TABLES and DBA_INDEXES, it would run more quickly, and would report tablespaces with rollback segments as well.

Jery K. writes: I keyed in the script and it ran. This is a very useful script to run automatically to monitor tablespaces.

About the Author

James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle and SQL