Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Tablespace / filesystem monitoring script

SOLVED
Go to solution
Philip Dunn_1
Frequent Advisor

Tablespace / filesystem monitoring script

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
7 REPLIES
Oviwan
Honored Contributor

Re: Tablespace / filesystem monitoring script

Hi

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

Regards
Peter Godron
Honored Contributor

Re: Tablespace / filesystem monitoring script

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
Honored Contributor

Re: Tablespace / filesystem monitoring script

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.


Bill Hassell, sysadmin
Philip Dunn_1
Frequent Advisor

Re: Tablespace / filesystem monitoring script

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
Respected Contributor

Re: Tablespace / filesystem monitoring script

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 :)
Look before you leap
Frank de Vries
Respected Contributor

Re: Tablespace / filesystem monitoring script

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
Look before you leap
Peter Godron
Honored Contributor
Solution

Re: Tablespace / filesystem monitoring script

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