Tablespace / filesystem monitoring script

[Philip Dunn_1]

Hi,
I would like to write a premptive script, that monitors the freespace within all tablespaces of an oracle database and also the filesystems in which the datafiles reside, alerting us when both reach predefined levels. This is so we can find and allocate space on the arrays in advance. Does anyone have any experience of writing such a script? and could offer some advice on where to start?
Do you think it could it be written in plain shell and sql or would I have to delve into perl?
Thanks for any advice or sample scripts you can offer

Phil

[Oviwan]

Hi

Take a look at these scripts:
http://www.orafaq.com/scripts/index.htm#SPACE

Regards

[Peter Godron]

Phil,
lots of scripts available via search engine of your choice.

I would set the database to autoextend, so you just have to worry about disk storage level. That also gets you around the potential problem of hardcoded Oracle usernames/passwords.

As the simplest solution I would use cron and bdf.

[Bill Hassell]

Here is a script that you can run in cron every 5 or 10 minutes to check on the size of all mounted lvols. It will only send one email once the threshold has been reached and another email if the size continues to grow beyond a growth value. This prevents continuous emails until the problem is fixed. Each mounted filesystem can have a separate limit and growth value. See the comments.

[Philip Dunn_1]

Thanks for all your replys, they have certainly given me something to go on. I must admit I was surprised that I haven't found a script to do exactly what I want to do on internet searches. I can't be the only one that would like to do this?
Oh well, I'll let you know what I come up with.
Thanks again

Phil

[Frank de Vries]

A bit later, but maybe still interesting:)
This is a very cool sql script for your
tablespaces I have been using over the years.
I build a unix script around it , but it
all boils down to knowing your % free .

To bypass password in script you can use
a ops$account from unix or windows.

Keep cooking :)

[Frank de Vries]

This is the syntax:

select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) "%_free",
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes

[Peter Godron · Accepted solution]

Phil,
not wanting to disappoint you I have concatinated a number of sources into:

#!/usr/bin/sh
export ORACLE_HOME=
export ORACLE_SID=

file=/tmp/${$}_`date +%Y%m%d`

sqlplus -s system/manager << .eof > $file
set pages 0
select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) "%_free",
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) > 89;
exit;
.eof

if [ -s $file ]
then
mailx -s"database level" emailid < $file
fi
rm $file

bdf|awk '{print $5," ",$6}'|sed 's/%/ /g'|awk '$1>89{print $1," ",$2}'|sed '1d' > $file
if [ -s $file ]
then
mailx -s"bdf level" emailid < $file
fi
rm $file