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, 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 ;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 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
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.