contrib/check_ora_table_space.pl

   1 #!/usr/bin/perl
   2 #
   3 # Program check_ora_table_space
   4 # Written by: Erwan Arzur (erwan@netvalue.com)
   5 # License: GPL
   6 #
   7 # Last Modified: $Date$
   8 # Revisiin: $Revision$
   9 #
  10 # "check_ora_table_space.pl" plugin to check the state of Oracle
  11 # table spaces. Scarce documentation.
  12 #
  13 # you need DBD-Oracle-1.03.tar.gz and DBI-1.13.tar.gz from CPAN.org as
  14 # well as some Oracle client stuff to use it.
  15 #
  16 # The SQL request comes from www.dbasupport.com
  17 #
  18
  19 use DBI;
  20 $ENV{"ORACLE_HOME"}="/intranet/apps/oracle";
  21
  22 my $host = shift || &usage ("no host specified");
  23 my $sid  = shift || &usage ("no sid specified");
  24 my $port = shift || &usage ("no port specified");
  25 my $dbuser = shift || &usage ("no user specified");
  26 my $dbpass = shift || &usage ("no password specified");
  27 my $tablespace = shift || &usage ("no table space  specified");
  28
  29 my $alertpct = int(shift) || &usage ("no warning state percentage specified");
  30 my $critpct =  int(shift) || &usage ("no critical state percentage specified");
  31
  32 my $dbh = DBI->connect( "dbi:Oracle:host=$host;port=$port;sid=$sid", $dbuser, $dbpass, { PrintError => 0, AutoCommit => 1, RaiseError => 0 } )
  33         || &error ("cannot connect to $dbname: $DBI::errstr\n");
  34
  35 #$sth = $dbh->prepare(q{SELECT tablespace_name, SUM(BYTES)/1024/1024 FreeSpace FROM dba_free_space group by tablespace_name})
  36 my $exit_code = -1;
  37 $sth = $dbh->prepare(<<EOF
  38 select a.TABLESPACE_NAME, a.total,nvl(b.used,0) USED,
  39 nvl((b.used/a.total)*100,0) PCT_USED
  40 from (select TABLESPACE_NAME, sum(bytes)/(1024*1024) total
  41 from sys.dba_data_files group by TABLESPACE_NAME) a,
  42 (select TABLESPACE_NAME,bytes/(1024*1024) used from sys.SM\$TS_USED) b
  43 where  a.TABLESPACE_NAME='$tablespace' and
  44  a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
  45 EOF
  46 )
  47         || &error("Cannot prepare request : $DBI::errstr\n");
  48 $sth->execute
  49         || &error("Cannot execute request : $DBI::errstr\n");
  50
  51 while (($tbname, $total, $used, $pct_used) = $sth->fetchrow)
  52 {
  53         $pct_used=int($pct_used);
  54         print STDOUT "size: " . $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)\n";
  55         #print "table space $answer\n";
  56         if ($pct_used > $alertpct) {
  57                 if ($pct_used > $critpct) {
  58                         $exit_code = 2
  59                 } else {
  60                         $exit_code = 1;
  61                 }
  62         } else {
  63                 $exit_code = 0;
  64         }
  65 }
  66
  67 $rc = $dbh->disconnect
  68         || &error ("Cannot disconnect from database : $dbh->errstr\n");
  69
  70 exit ($exit_code);
  71
  72 sub usage {
  73         print "@_\n" if @_;
  74         print "usage : check_ora_table_space.pl <host> <sid> <port> <user> <passwd> <tablespace> <pctwarn> <pctcrit>\n";
  75         exit (-1);
  76 }
  77
  78 sub error {
  79         print "@_\n" if @_;
  80         exit (2);
  81 }
  82