Oracle tablespace check

Project Notes

This is a tablespace-check for Oracle databases. I was looking for a good check that automatically checks all Oracle tablespaces in a given database and found only one: Good old check_oracle_health. However, when using that, it proved to be so heavy on many databases that it was unusable. After i implemented the check in nagios, Oracle-databases became periodically unreachable because of high cpu usage, caused by the check. However, there was no lighter alternative with the same functionality. I needed a check that checks all tablespaces in a given database, like check_oracle_health does. There was none. So in the end, i decided to write my own. And here it is. Because i think other people need this too, i decided to share it.

This check gives you the same tablespace-check functionality as check_oracle_health, but without the high cpu-usage.

The check first gathers all tablespaces in an oracle database, then checks every tablespace against the given trigger-levels. It outputs the tablespaces that give a Warning or Critical and also reports the size of that tablespace, used space and free space left. It also reports the current use of that tablespace as percentage. If no tablespaces are in a Warning or Critical state, the check outputs "All tabelspaces are OK". So, you can simply give the check the name "check_tablespaces_<database-name> in Nagios, then you can see which database it checks in the Nagios-dashboard.

I made the check so, that it takes into account the possibility that tablespaces can span multiple datafiles, so the check also looks at the current size of each tablespace and adapts accordingly. Therefore, it only reports a full tablespace when a datafile is nearly reaching 32GB. Warning and Critical levels can be given through the commandline. If no levels are given, the check reports "Incorrect commandline. Usage: check_tablespaces <warnperc> <critperc>".
If the check is unable to login to the database for whatever reason, the check wil report "Error accessing database, check log." The check logs in textfiles in the current directory. It also creates some temporary files, but these are removed at the end of each run, with te exception of a file "tablespaces5_<DBNAME>.txt, that i leave on disk for debugging and as log. It is overwritten on each run. The check uses SQLplus for accessing the databases and needs a tnsnames.ora. You can modify the variable ORACLE_HOME in the check to set the correct location.
To make it clear what you need to change: I put a little comment at 3 locations in the script saying "# you probably need to change this #" to make it clear what you need to do to make it work as intended. Just edit the script to make it suit your needs. Have fun with it.