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

Directory Tree

check_oracle_tablespace

Rating
6 votes
Favoured:
1
Hits
132940
Files:
FileDescription
check_oracle_tablespace_1_10.tar.gzcheck_oracle_tablespace 1.10
Nagios CSP

Meet The New Nagios Core Services Platform

Built on over 25 years of monitoring experience, the Nagios Core Services Platform provides insightful monitoring dashboards, time-saving monitoring wizards, and unmatched ease of use. Use it for free indefinitely.

Monitoring Made Magically Better

  • Nagios Core on Overdrive
  • Powerful Monitoring Dashboards
  • Time-Saving Configuration Wizards
  • Open Source Powered Monitoring On Steroids
  • And So Much More!
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.