#!/bin/sh

################################
#   Check Oracle tablespaces   #
#   05-2020 by RK              #
################################

if [ "$1" == "" ]; then 
    echo "Incorrect commandline. Usage: check_tablespaces <warnperc> <critperc>"
    exit 1
fi
setwarn="$1"
setcrit="$2"

DB="<put your databasename here>"

# you probably need to change this #
export ORACLE_HOME=/oracle/ora1/app/oracle/12.2

# you probably need to change this #
cd /nagios/oracle_tablespaces

# you probably need to change the sqlplus output path #
echo "select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;" | sqlplus -s nagios/<Password comes here>@$DB > /nagios/oracle_tablespaces/tablespaces1_$DB.txt

cat tablespaces1_$DB.txt | sed -r '/^\s*$/d' > tablespaces2_$DB.txt
cat tablespaces2_$DB.txt | sed '/---------/d' > tablespaces3_$DB.txt
cat tablespaces3_$DB.txt | sed '/TABLESPACE/d' > tablespaces4_$DB.txt
cat tablespaces4_$DB.txt | head -n -1 > tablespaces5_$DB.txt

cat tablespaces5_$DB.txt | grep "ERROR" | head -n -1 > /tmp/checkok.tmp
if [ "$(sed -n '/ERROR/p' /tmp/checkok.tmp)" == "ERROR:" ]; then
    echo "Error accessing database, check log."
    exit 1
fi

echo "" > ${DB}_result.tmp

##########################################
###            Loop Start              ###
##########################################

cat tablespaces5_$DB.txt | while read LINE; do 
name=`echo $LINE | awk '{print $1;}'`
size1=`echo $LINE | awk '{print $2;}'`
free1=`echo $LINE | awk '{print $3;}'`

if [[ $free1 == "" ]]; then
free1="$size1"
fi
size=`echo $size1 | awk '{print int($1+0.5)}'`
free=`echo $free1 | awk '{print int($1+0.5)}'`

used=$(echo "$size - $free" | bc)

extents=32000
while [ $extents -le $size ]; do
extents=$(( $extents + 32000 ))
done

# for debugging
# echo "------"
# echo "naam is $name"
# echo "free is $free"
# echo "extents is $extents"
# echo "size is $size"
# echo "used is $used"

result=""

used2=$(echo "$extents - $used" | bc)
# echo "used2 is $used2"

percent=$(awk "BEGIN { pc=100*${used}/${extents}; i=int(pc); print (pc-i<0.5)?i:i+1 }")

# echo "percent is $percent"

if [[ $percent -gt "$setwarn" ]];then
    result="WARNING"
    if [[ $(cat ${DB}_result.tmp) != "CRITICAL" ]]; then
        echo "WARNING" > ${DB}_result.tmp
    fi
fi
if [[ $percent -gt "$setcrit" ]];then
    result="CRITICAL"
    echo "CRITICAL" > ${DB}_result.tmp
fi

# echo "result in loop is $result"

if [[ $result == "CRITICAL" ]];then
    echo "CRITICAL - Tablespace $name is $percent% full - Size is $size MB, Used space is $used MB, Free space is $free MB."
fi
if [[ $result == "WARNING" ]];then
    echo "WARNING - Tablespace $name is $percent% full - Size is $size MB, Used space is $used MB, Free space is $free MB."
fi
done

###################################
###           Loop End          ###
###################################

# Cleanup the mess we made #

rm -f ./tablespaces1_${DB}.txt
rm -f ./tablespaces2_${DB}.txt
rm -f ./tablespaces3_${DB}.txt
rm -f ./tablespaces4_${DB}.txt
# rm -f ./tablespaces5_${DB}.txt
# i leave tablespaces5_${DB}.txt for informational and debugging purposes #

#################################

# Set correct exitlevel #

einde=$(cat ${DB}_result.tmp)

if [[ $einde == "CRITICAL" ]]; then
# rm -f ${DB}_result.tmp
exit 2
fi

if [[ $einde == "WARNING" ]]; then
# rm -f ${DB}_result.tmp
exit 1
fi

if [[ $einde == "" ]]; then
echo "All tablespaces OK"
# rm -f ${DB}_result.tmp
exit 0
fi