Home Directory Plugins Databases Oracle check_oracle_tablespace

Search Exchange

Search All Sites

Nagios Live Webinars

Let our experts show you how Nagios can help your organization.

Contact Us

Phone: 1-888-NAGIOS-1
Email: sales@nagios.com

Login

Remember Me

check_oracle_tablespace

Rating
6 votes
Favoured:
1
Hits
121157
Files:
FileDescription
check_oracle_tablespace_1_10.tar.gzcheck_oracle_tablespace 1.10
Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
Nagios plugin to check Oracle tablespace usage
This Nagios plugin checks Oracle tablespace usage. It makes an
SQL query using Oracle's sqlplus command to calculate
tablespace usage percentages for given Oracle SID and databases.
Using '-a' option makes plugin autoextension aware, e.g. usage
percentage is determined by comparing used space against maximum
tablespace size allowed by autoextension, not the current size.

Examples:

# check_oracle_tablespace.sh -s SID -d 'FOO.*' -w 80 -c 90
TABLESPACE CRITICAL: FOODB1 98% WARNING: FOODB2 82%; FOODB3 84%

# check_oracle_tablespace.sh -s SID -d 'FOO.*' -w 80 -c 90 -a
TABLESPACE CRITICAL: FOODB1 AUTOEXT 91%

Reviews (4)
The problem with the original query is if you have a tablespace with mixed autoextend and fixed size datafiles (it's rare, but it's possible).
If you want to fix it, you can replace the query from lines 247 to 282 with this one that I've made:
select
z.TABLESPACE_NAME,
round(((Mbytes_used - Mbytes_free) / Mbytes_used) * 100) usage_pct,
round(decode(MAXMBYTES, 34359721984, 0, (Mbytes_used - Mbytes_free) / MAXMBYTES * 100)) max_pct,
case
when (select count(distinct AUTOEXTENSIBLE) from dba_data_files where TABLESPACE_NAME = z.TABLESPACE_NAME) > 1 then 'YES/NO'
else (select distinct AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME = z.TABLESPACE_NAME)
end as AUTOEXTENSIBLE
from
( select TABLESPACE_NAME,
sum (Mbytes_used) Mbytes_used,
sum (Mbytes_free) Mbytes_free,
sum (MAXMBYTES) MAXMBYTES
from
(
select substr(df.TABLESPACE_NAME,1,length(df.TABLESPACE_NAME)-4) TABLESPACE_NAME,
df.BYTES/1024/1024 Mbytes_used,
nvl(fs.BYTES/1024/1024, 0) Mbytes_free,
df.MAXBYTES/1024/1024 MAXMBYTES,
df.AUTOEXTENSIBLE
from
(
select TABLESPACE_NAME||LPAD(FILE_ID,4,0) TABLESPACE_NAME,
sum(BYTES) BYTES,
AUTOEXTENSIBLE,
decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
from dba_data_files
group by TABLESPACE_NAME||LPAD(FILE_ID,4,0),
AUTOEXTENSIBLE
)
df
LEFT OUTER JOIN
(
select a.TABLESPACE_NAME||LPAD(FILE_ID,4,0) TABLESPACE_NAME,
sum(a.BYTES) BYTES
from dba_free_space a
group by TABLESPACE_NAME||LPAD(FILE_ID,4,0)
)
fs
ON df.TABLESPACE_NAME=fs.TABLESPACE_NAME
order by df.TABLESPACE_NAME desc
) a
group by TABLESPACE_NAME
) z
order by 1 asc
/
byHulskamp, November 6, 2013
The script works if there are multiple datafiles. With one datafile and autoextension enabled, for some reason the creator opted to default to 0 for the total percentage used by a tablespace. I have no idea why.

If you apply this patch, everything seems to work:

--- check_oracle_tablespace.sh 2012-01-16 14:06:38.000000000 +0100
+++ check_oracle_tablespace.sh 2013-11-06 14:40:44.000000000 +0100
@@ -259,7 +259,7 @@

select df.TABLESPACE_NAME,
round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct,
- round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.MAXBYTES * 100)) max_pct,
+ round(((df.BYTES - fs.BYTES) / df.MAXBYTES) * 100) max_pct,
df.AUTOEXTENSIBLE
from
(
byleonhou, November 20, 2012
So far, I got my nagios worked for monitor a remote table space! Thanks so so so much!
bynologo, July 16, 2009
1 of 1 people found this review helpful
Very good plugin that does exactly what it says. The only disadvantage is that I prefer larger plugins that accomplish far more; no one really wants to test tons of different plugins from different developers before they put them in their prod environments.