check_oracle_tablespace

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
/

---

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
(

---

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.

---