#!/usr/bin/perl
#
# Program check_ora_table_space
# Written by: Erwan Arzur (erwan@netvalue.com)
# License: GPL
#
# Last Modified: $Date: 2002/02/28 06:42:54 $
# Revisiin: $Revision: 1.1.1.1 $
#
# "check_ora_table_space.pl" plugin to check the state of Oracle 
# table spaces. Scarce documentation.
#
# you need DBD-Oracle-1.03.tar.gz and DBI-1.13.tar.gz from CPAN.org as
# well as some Oracle client stuff to use it.
#
# The SQL request comes from www.dbasupport.com
#

require 5.004;
use strict;
use lib utils.pm ;
use Plugin;
use Plugin::Parameter qw(:DEFAULT $u_opt $P_opt $p_opt :thresholds);

use DBI;

use vars qw($opt_H $opt_s $opt_u $opt_P $opt_T $opt_p $opt_t $opt_w $opt_c $PROGNAME);

use utils qw(%ERRORS);

my $s_opt = new Plugin::Parameter(-name => "sid", -flags => [ 's', 'sid' ],
				  -optional => "no", -valueoptional => "no", -type => "SID",
				  -description => "SID");
my $T_opt = new Plugin::Parameter(-name => "tablespace", -flags => [ 'T', 'tablespace' ],
				  -optional => "no", -valueoptional => "no", -type => "TABLESPACE",
				  -description => "TABLESPACE");
my $plugin = new Plugin(-revision => '$Revision: 1.1.1.1 $',
			-copyright => "2002 Erwan Arzur <erwan\@netvalue.com>, 2004 Howard Wilkinson <howard\@cohtech.com>",
			-shortcomment => "Plugin to check the state of Oracle table spaces",
			-parameterlists => [ [ $H_opt, $s_opt, $p_opt, $u_opt, $P_opt, $T_opt, $w_opt, $c_opt, $t_opt ], $h_opts, $V_opts ]);

$ENV{"ORACLE_HOME"}="/intranet/apps/oracle";

my $dbh = DBI->connect(	"dbi:Oracle:host=$opt_H;port=$opt_p;sid=$opt_s", $opt_u, $opt_P, { PrintError => 0, AutoCommit => 1, RaiseError => 0 } )
	|| &error ("cannot connect to Oracle: $DBI::errstr\n");

my $exit_code = 'UNKNOWN';

my $sth = $dbh->prepare(<<EOF
select a.TABLESPACE_NAME, a.total,nvl(b.used,0) USED, 
nvl((b.used/a.total)*100,0) PCT_USED 
from (select TABLESPACE_NAME, sum(bytes)/(1024*1024) total 
from sys.dba_data_files group by TABLESPACE_NAME) a, 
(select TABLESPACE_NAME,bytes/(1024*1024) used from sys.SM\$TS_USED) b 
where  a.TABLESPACE_NAME='$opt_T' and 
 a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
EOF
		    )
  || &error("Cannot prepare request : $DBI::errstr\n");
$sth->execute
  || &error("Cannot execute request : $DBI::errstr\n");

my $tbname = undef;
my $total = undef;
my $used = undef;
my $pct_used = undef;
while (($tbname, $total, $used, $pct_used) = $sth->fetchrow) {
	$pct_used=int($pct_used);
	#print "table space $answer\n";
	if ($pct_used > $opt_w) {
	        if ($pct_used > $opt_c) {
			$exit_code = 'CRITICAL';
		} else {
			$exit_code = 'WARNING';
		}
	} else {
		$exit_code = 'OK';	
	}
}

my $rc = $dbh->disconnect
	|| &error ("Cannot disconnect from database : $dbh->errstr\n");

print "$PROGNAME $exit_code: size: " . $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)\n";
exit $ERRORS{$exit_code};

sub error {
	print "$PROGNAME UNKNOWN: @_\n" if @_;
	exit $ERRORS{'UNKNOWN'};
}