#!/usr/bin/python

# check_oracle_tablespace.py
# Copyright (C) 2018 Curtis John
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
 
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import os
import cx_Oracle
import argparse
import time

#declare variables
status = ['OK:', 'WARNING:', 'CRITICAL:', 'UNKNOWN:']

#add arguments
parser = argparse.ArgumentParser()
parser.add_argument('-C', '--connector', required=True, type=str, help='connection string in USER/PASSWORD@DATABASE format')
parser.add_argument('-T', '--tablespace', required=True, type=str, help='name of the tablespace you wish to check')
parser.add_argument('-w', '--warning', default=80, type=int, help='warning threshold percent. default 80')
parser.add_argument('-c', '--critical', default=90, type=int, help='critical threshold percent. default 90')
parser.add_argument('-t', '--timeout', default=30, type=int, help='check timeout in seconds. default 30')

#parse arguments into array
args = parser.parse_args()

#assign our arguments to variables
connector	=	args.connector
tablespace	=	args.tablespace
warning 	=	args.warning
critical	=	args.critical
timeout		=	args.timeout

if not warning <= critical:
    print('The warning threshold must be less than or equal to the critical threshold')
    exit(3)

#these environment variables need to be set either within the script,
#or within the OS (system or user level):

#os.putenv('ORACLE_HOME', '/usr/lib/oracle/12.1/client64')
#os.putenv('LD_LIBRARY_PATH', '/usr/lib/oracle/12.1/client64')
	
#test connection to db using connect argument
try:
  con = cx_Oracle.connect (connector)
except cx_Oracle.DatabaseError:
	print status[3]+" check --connector argument formatting"
	printException(exception)
	exit(3)
 
#prep temp tablespace evaluation
tempeval =	"SELECT tablespace_name " \
			"FROM dba_temp_files " \
			"WHERE tablespace_name = '%s'" % tablespace

#create cursor to initiate db connection
curse = con.cursor()

#execute temp tablespace evaluation
curse.execute(tempeval)
#establish boolean for temp tablespaces
tempcount = 0
for result in curse:
	if result[0] == tablespace:
		tempcount = 1
if tempcount > 0:
	#temporary tablespace has been detected WEE-WOO-WEE-WOO
	#test for auto-extensibility on temp tablespace
	#there is very little reason for a temp tablespace to be auto-extensible,
	#however it can happen.
	tbltempauto	=	"SELECT AUTOEXTENSIBLE " \
					"FROM dba_temp_files " \
					"WHERE tablespace_name = '%s'" % tablespace

	curse.execute(tbltempauto)
	for result in curse:
		if result[0] == "NO":
			#prep temp non-auto-extensible tablespace usage percentage sql
			usagesql =	"SELECT round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used, " \
						"round((df.bytes - sum(fs.bytes)) / (1024 * 1024)) used_ts_size, " \
						"round(df.maxbytes / (1024 * 1024), 2) max_ts_size " \
						"FROM (select tablespace_name, bytes_used bytes from V$temp_space_header " \
						"group by tablespace_name, bytes_free, bytes_used) fs, " \
						"(select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes " \
						"from dba_temp_files group by tablespace_name) df " \
						"WHERE fs.tablespace_name = df.tablespace_name " \
						"AND df.tablespace_name = '%s' " \
						"GROUP BY df.tablespace_name, df.bytes, df.maxbytes" % tablespace
			
			curse.execute(usagesql)
			for result in curse:
			#set var for percentage of tablespace used
				usagepct = result[0]
				tblusg = result[1]
				tblalloc = result[2]
				con.close()
		
		elif result[0] == "YES":
			#prep temp auto-extensible tablespace usage percentage sql
			usagesql =	"SELECT round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used, " \
						"round((df.bytes - sum(fs.bytes)) / (1024 * 1024)) used_ts_size, " \