summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--contrib/check_oracle_tbs62
1 files changed, 37 insertions, 25 deletions
diff --git a/contrib/check_oracle_tbs b/contrib/check_oracle_tbs
index bcc4af8..0efdd1e 100644
--- a/contrib/check_oracle_tbs
+++ b/contrib/check_oracle_tbs
@@ -1,9 +1,7 @@
1#!/usr/local/bin/perl -w 1#!/usr/local/bin/perl -w
2 2
3# (c)2003 John Koyle, RFP Depot, LLC. 3# (c)2004 John Koyle, RFP Depot, LLC.
4# This is free software use it however you would like. 4# This is free software use it however you would like.
5# Thanks to the folks at http://www.think-forward.com for the SQL query
6
7 5
8use strict; 6use strict;
9use DBI; 7use DBI;
@@ -25,10 +23,10 @@ my $orapwd = "";
25 23
26 24
27if (!$ENV{ORACLE_HOME}) { 25if (!$ENV{ORACLE_HOME}) {
28 $ENV{ORACLE_HOME} = '/a01/app/oracle/product/9.2.0.1'; 26 $ENV{ORACLE_HOME} = '/u01/app/oracle/product/9.2';
29} 27}
30 28
31#*****************You shouldn't need to modify anything below here ************* 29#*******************************************************************************
32my $state = $ERRORS{'UNKNOWN'}; 30my $state = $ERRORS{'UNKNOWN'};
33my $answer = undef; 31my $answer = undef;
34 32
@@ -37,10 +35,12 @@ my $VERSION = sprintf("%d.%02d", $MAJOR_VERSION - 1, $MINOR_VERSION);
37 35
38my $opt_debug; # -d|--debug 36my $opt_debug; # -d|--debug
39my $opt_help; # -h|--help 37my $opt_help; # -h|--help
40my $opt_version; # -V|--version 38my $opt_version; # -V|--version
41my $opt_warn_space; # -w|--warn-space 39my $opt_warn_space; # -w|--warn-space
42my $opt_crit_space; # -c|--crit-space 40my $opt_crit_space; # -c|--crit-space
43 41
42
43
44my $help = <<MARK; # help statement 44my $help = <<MARK; # help statement
45 45
46check_oracle_tbs v$VERSION 46check_oracle_tbs v$VERSION
@@ -60,6 +60,7 @@ MARK
60 60
61Getopt::Long::config('no_auto_abbrev', 'no_ignore_case'); 61Getopt::Long::config('no_auto_abbrev', 'no_ignore_case');
62 62
63
63my $rc = GetOptions( 64my $rc = GetOptions(
64 "debug|d" => \$opt_debug, 65 "debug|d" => \$opt_debug,
65 "help|h" => \$opt_help, 66 "help|h" => \$opt_help,
@@ -103,17 +104,18 @@ if (! defined $opt_crit_space)
103 104
104my $array_ref = executeSQL(); 105my $array_ref = executeSQL();
105 106
107# Don't match certain tablespaces.
106foreach my $row (@$array_ref) { 108foreach my $row (@$array_ref) {
107 my ( $tbs_name, $tot_mb, $free_mb, $free_pct, $used_pct, $fsfi) = @$row; 109 my ( $tbs_name, $free_mb, $tot_mb, $free_pct) = @$row;
108 if ($opt_debug) { print STDOUT "Output: $tbs_name\t$tot_mb\t$free_mb\t$free_pct\t$used_pct\t$fsfi\n\n"; } 110 if ($opt_debug) { print STDOUT "Output: $tbs_name\t$tot_mb\t$free_mb\t$free_pct\n\n"; }
109 if ($used_pct > (100 - $opt_crit_space) && $tbs_name !~ /RBS/) { 111 if ($free_pct < $opt_crit_space && $tbs_name !~ /RBS/ && $tbs_name !~ /PERFSTAT/ && $tbs_name !~ /UNDOTBS/) {
110 $state = $ERRORS{'CRITICAL'}; 112 $state = $ERRORS{'CRITICAL'};
111 $answer .= "$tbs_name = $used_pct\% "; 113 $answer .= "Critical: $tbs_name = $free_pct\% ";
112 last; 114 last;
113 } 115 }
114 if ($used_pct > (100 - $opt_warn_space) && $tbs_name !~ /RBS/) { 116 if ($free_pct < $opt_warn_space && $tbs_name !~ /RBS/ && $tbs_name !~ /PERFSTAT/ && $tbs_name !~ /UNDOTBS/) {
115 $state = $ERRORS{'WARNING'}; 117 $state = $ERRORS{'WARNING'};
116 $answer .= "$tbs_name = $used_pct\% "; 118 $answer .= "Warning: $tbs_name = $free_pct\% ";
117 } 119 }
118} 120}
119 121
@@ -132,6 +134,7 @@ foreach my $key (keys %ERRORS) {
132} 134}
133exit $state; 135exit $state;
134 136
137#------------------SUBS-------------------------------------------------------
135sub executeSQL 138sub executeSQL
136{ 139{
137 my ($dbh, $sth, $results); 140 my ($dbh, $sth, $results);
@@ -140,20 +143,29 @@ sub executeSQL
140 143
141 eval { 144 eval {
142 $dbh->{RaiseError} = 1; 145 $dbh->{RaiseError} = 1;
143 # This query is taken from this URL and used with permission: http://www.think-forward.com/sql/tspace.htm
144 $sth = $dbh->prepare(q{ 146 $sth = $dbh->prepare(q{
145 select df.tablespace_name tspace, 147 select ts.tablespace_name,
146 df.bytes/(1024*1024) tot_ts_size, 148 trunc(sum(ts.free_b)/1024/1024) free,
147 sum(fs.bytes)/(1024*1024) free_ts_size, 149 trunc(sum(ts.max_b)/1024/1024) total,
148 round(sum(fs.bytes)*100/df.bytes) ts_pct, 150 trunc( sum(ts.free_b)/sum(ts.max_b)*1000) / 10 as pct_free
149 round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1, 151 from
150 ROUND(100*SQRT(MAX(fs.bytes)/SUM(fs.bytes))* 152 (select a.file_id,
151 (1/SQRT(SQRT(COUNT(fs.bytes)))) ,2) FSFI 153 a.tablespace_name,
152 from dba_free_space fs, (select tablespace_name, sum(bytes) bytes 154 decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.free) free_b,
153 from dba_data_files 155 a.maxsize max_b
154 group by tablespace_name ) df 156 from (select file_id,
155 where fs.tablespace_name = df.tablespace_name 157 tablespace_name,
156 group by df.tablespace_name, df.bytes 158 autoextensible,
159 bytes,
160 decode(autoextensible,'YES',maxbytes,bytes) maxsize
161 from dba_data_files) a,
162 (select file_id,
163 tablespace_name,
164 sum(bytes) free
165 from dba_free_space
166 group by file_id, tablespace_name) b
167 where a.file_id=b.file_id(+)) ts
168 group by tablespace_name
157 }); 169 });
158 170
159 $sth->execute(); 171 $sth->execute();