Search Exchange
Search All Sites
Nagios Live Webinars
Let our experts show you how Nagios can help your organization.Login
New ListingsDirectory Tree
check_oracle_tablespace
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%
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)
byvegatripy, August 11, 2015
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
/
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
(
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!
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.
