Basic Oracle space monitoring scripts

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,
        decode(
            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  ;

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

  select
 fre.tablespace_name,
 sum(fre.bytes/1024) free_space,
 count(*) num_free,
 max(fre.bytes/1024) largest,
 /*avg(fre.bytes/1024) avg_size,*/
 greatest(nvl(mnt.max_next_extent,&block_size),
   nvl(mni.max_next_extent,&block_size))/1024 grt_extent,
 sum(decode(greatest(
  greatest(nvl(mnt.max_next_extent,&block_size),
                    nvl(mni.max_next_extent,&block_size)),
  fre.bytes), fre.bytes,
  trunc(fre.bytes/greatest(nvl(mnt.max_next_extent,&block_size),
   nvl(mni.max_next_extent,&block_size))),0)) min_usable
  from
 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
  where
 fre.tablespace_name = mnt.tablespace_name(+) and
    fre.tablespace_name = mni.tablespace_name(+)
  group by
 fre.tablespace_name,
 greatest(nvl(mnt.max_next_extent,&block_size),
          nvl(mni.max_next_extent,&block_size))
  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 ]
  then
    echo Filesystem /arch is at $SPACE_USED percent | mail jimg
  fi

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

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.