diff options
Diffstat (limited to 'contrib/check_ora_table_space.pl')
| -rw-r--r-- | contrib/check_ora_table_space.pl | 82 |
1 files changed, 82 insertions, 0 deletions
diff --git a/contrib/check_ora_table_space.pl b/contrib/check_ora_table_space.pl new file mode 100644 index 00000000..24497b24 --- /dev/null +++ b/contrib/check_ora_table_space.pl @@ -0,0 +1,82 @@ +#!/usr/bin/perl +# +# Program check_ora_table_space +# Written by: Erwan Arzur (erwan@netvalue.com) +# License: GPL +# +# Last Modified: $Date$ +# Revisiin: $Revision$ +# +# "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 +# + +use DBI; +$ENV{"ORACLE_HOME"}="/intranet/apps/oracle"; + +my $host = shift || &usage ("no host specified"); +my $sid = shift || &usage ("no sid specified"); +my $port = shift || &usage ("no port specified"); +my $dbuser = shift || &usage ("no user specified"); +my $dbpass = shift || &usage ("no password specified"); +my $tablespace = shift || &usage ("no table space specified"); + +my $alertpct = int(shift) || &usage ("no warning state percentage specified"); +my $critpct = int(shift) || &usage ("no critical state percentage specified"); + +my $dbh = DBI->connect( "dbi:Oracle:host=$host;port=$port;sid=$sid", $dbuser, $dbpass, { PrintError => 0, AutoCommit => 1, RaiseError => 0 } ) + || &error ("cannot connect to $dbname: $DBI::errstr\n"); + +#$sth = $dbh->prepare(q{SELECT tablespace_name, SUM(BYTES)/1024/1024 FreeSpace FROM dba_free_space group by tablespace_name}) +my $exit_code = -1; +$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='$tablespace' and + a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) +EOF +) + || &error("Cannot prepare request : $DBI::errstr\n"); +$sth->execute + || &error("Cannot execute request : $DBI::errstr\n"); + +while (($tbname, $total, $used, $pct_used) = $sth->fetchrow) +{ + $pct_used=int($pct_used); + print STDOUT "size: " . $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)\n"; + #print "table space $answer\n"; + if ($pct_used > $alertpct) { + if ($pct_used > $critpct) { + $exit_code = 2 + } else { + $exit_code = 1; + } + } else { + $exit_code = 0; + } +} + +$rc = $dbh->disconnect + || &error ("Cannot disconnect from database : $dbh->errstr\n"); + +exit ($exit_code); + +sub usage { + print "@_\n" if @_; + print "usage : check_ora_table_space.pl <host> <sid> <port> <user> <passwd> <tablespace> <pctwarn> <pctcrit>\n"; + exit (-1); +} + +sub error { + print "@_\n" if @_; + exit (2); +} + |