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 0000000..24497b2 --- /dev/null +++ b/contrib/check_ora_table_space.pl | |||
@@ -0,0 +1,82 @@ | |||
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 | |||