-
Notifications
You must be signed in to change notification settings - Fork 13
/
Copy pathcheck_tablespace_oracle.java
175 lines (154 loc) · 8.91 KB
/
check_tablespace_oracle.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
// ----------------------------------------------------------------------------
// check_tablespace_oracle.java 20160904 frank4dd version 1.1
// ----------------------------------------------------------------------------
// e-mail: support[at]frank4dd.com
// web: http://nagios.fm4dd.com/
//
// This nagios plugin queries the Oracle dba_free_space and dba_data_files
// system tables. Supported are Oracle versions 10g and up.
//
// Pre-requisites: Oracle JDBC driver installed and a valid DB user.
// ----------------------------------------------------------------------------
// Example Output:
// > java check_tablespace_oracle 127.0.0.1 1521 XE system test -d
// DB connect: jdbc:oracle:thin:system/[email protected]:1521:XE
// DB query: select df.TABLESPACE_NAME, df.FILE_ID, ((df.BYTES+fs.BYTES)/1024) bytes_max,
// (df.BYTES/1024) bytes_used, round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct
// from ( select TABLESPACE_NAME, sum(BYTES) BYTES, count(distinct FILE_ID) FILE_ID from
// dba_data_files group by TABLESPACE_NAME ) df, ( select TABLESPACE_NAME, sum(BYTES) BYTES
// from dba_free_space group by TABLESPACE_NAME) fs where df.TABLESPACE_NAME=fs.TABLESPACE_NAME
// order by df.TABLESPACE_NAME asc
// Name: SYSAUX Files: 1, Space total: 706.62 MB, Space used: 670.00 MB, % used: 95 %
// Name: SYSTEM Files: 1, Space total: 790.50 MB, Space used: 790.00 MB, % used: 100 %
// Name: UNDOTBS1 Files: 1, Space total: 215.56 MB, Space used: 215.00 MB, % used: 100 %
// Name: USERS Files: 1, Space total: 8.62 MB, Space used: 5.00 MB, % used: 28 %
// ----------------------------------------------------------------------------
// Revision history:
// 20100820 initial release version 1.0
// 20160904 bugfix: 2TB overflow: Tablespace size type Integer changed to Long
// base count changed to bytes, adding function for human readable format
import java.sql.*;
import java.text.*;
class check_tablespace_oracle {
static long bytes_warn = 0; // the commandline argument for warning threshold of B used
static long bytes_crit = 0; // the commandline argument for critical threshold of B used
static long bytes_used = 0; // the returned tablespace value of used space (in Bytes)
static long bytes_total= 0; // the returned tablespace value of total space available
static int dfiles_total= 0; // the returned number of tablespace files
static int percent_used= 0; // the returned tablespace value, current space used in percent
static int debug = 0; // 'normal'=>0,'verbose'=>1 when -d parameter is given
static String output = ""; // the plugin output string
static String perfdata = ""; // the plugin perfdata output
static String tbspname = ""; // the tablespace to check
static String dbUrl = ""; // the access URL for the database to query
static String query = ""; // the SQL query to execute
public static String floatForm (double d) {
return new DecimalFormat("0.00").format(d);
}
public static String bytesToHuman (long size) {
long Kb = 1 * 1024;
long Mb = Kb * 1024;
long Gb = Mb * 1024;
long Tb = Gb * 1024;
long Pb = Tb * 1024;
long Eb = Pb * 1024;
if (size < Kb) return floatForm(size) + " Bytes";
if (size >= Kb && size < Mb) return floatForm((double)size / Kb) + " KB";
if (size >= Mb && size < Gb) return floatForm((double)size / Mb) + " MB";
if (size >= Gb && size < Tb) return floatForm((double)size / Gb) + " GB";
if (size >= Tb && size < Pb) return floatForm((double)size / Tb) + " TB";
if (size >= Pb && size < Eb) return floatForm((double)size / Pb) + " PB";
if (size >= Eb) return floatForm((double)size / Eb) + " EB";
return "Out of bounds"; // return overflow error, we won't calculate beyond EB
}
public static void main (String args[]) {
if (args.length < 6) {
System.err.println("Error: Missing Arguments.");
System.err.println("Syntax: java check_tablespace_oracle <db-ip> <db-port> <db-instance> <db-user> <db-pwd> <tablespace-name> <bytes-warn> <bytes-crit>");
System.err.println(" java check_tablespace_oracle <db-ip> <db-port> <db-instance> <db-user> <db-pwd> -r <tablespace-name>");
System.err.println(" java check_tablespace_oracle <db-ip> <db-port> <db-instance> <db-user> <db-pwd> -d");
System.exit(-1);
}
// Check if we got a particular tablespace to check for
if (args.length == 6 && args[5].equals("-d")) { debug = 1;}
dbUrl = "jdbc:oracle:thin:" + args[3] + "/" + args[4] + "@" + args[0] +":" + args[1] +":" + args[2];
if (debug == 1) { System.out.println("DB connect: " + dbUrl); }
// Check if we just return the data without any values to compare to
if (args.length == 7 && args[5].equals("-r")) {
tbspname = args[6];
}
// Check if we got warn and crit values to check against
if (args.length == 8) {
tbspname = args[5];
bytes_warn = Long.parseLong(args[6]);
bytes_crit = Long.parseLong(args[7]);
}
try {
// use the Oracle JDBC driver
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.err.println("Error: JDBC Driver Problem.");
System.err.println (e);
System.exit (3);
}
try {
// open connection to database "jdbc:oracle:thin:@destinationhost:port:dbname", "dbuser", "dbpassword"
Connection connection = DriverManager.getConnection(dbUrl);
// build query
// table dba_data_files: TABLESPACE_NAME, FILE_NAME, BYTES, MAXBYTES, AUTOEXTENSIBLE
// dba_free_space: TABLESPACE_NAME, FILE_ID, BYTES
// Show free tablespace: Select tablespace_name, Sum(bytes/(1024)) "Total Free (KB) " From dba_free_space Group By tablespace_name;
// Show used tablespace: Select tablespace_name, Sum(bytes/(1024)) "Total Used (KB) " From dba_data_files Group By tablespace_name;
if (tbspname == "") {
query = "select df.TABLESPACE_NAME, df.FILE_ID, (df.BYTES+fs.BYTES) bytes_max, df.BYTES bytes_used, round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct from ( select TABLESPACE_NAME, sum(BYTES) BYTES, count(distinct FILE_ID) FILE_ID from dba_data_files group by TABLESPACE_NAME ) df, ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space group by TABLESPACE_NAME) fs where df.TABLESPACE_NAME=fs.TABLESPACE_NAME order by df.TABLESPACE_NAME asc";
} else {
query = "select df.TABLESPACE_NAME, df.FILE_ID, (df.BYTES+fs.BYTES) bytes_max, df.BYTES bytes_used, round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct from ( select TABLESPACE_NAME, sum(BYTES) BYTES, count(distinct FILE_ID) FILE_ID from dba_data_files where TABLESPACE_NAME = '" + tbspname + "' group by TABLESPACE_NAME) df, ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space group by TABLESPACE_NAME) fs where df.TABLESPACE_NAME=fs.TABLESPACE_NAME order by df.TABLESPACE_NAME asc";
}
if (debug == 1) { System.out.println ("DB query: " + query); }
// execute query
Statement statement = connection.createStatement ();
ResultSet rs = statement.executeQuery (query);
while ( rs.next () ) {
// get content from column "1 -4"
if (debug == 1) {
System.out.format ("Name: %-20s ", rs.getString (1)); // TBSP_NAME, VARCHAR2(30)
System.out.format ("Files: %d, ", rs.getInt(2)); // TBSP File count, NUMBER
System.out.format ("Space total: %10s, ", bytesToHuman(rs.getLong(3))); // TBSP_TOTAL_SIZE in B, NUMBER
System.out.format ("Space used: %10s, ", bytesToHuman(rs.getLong(4))); // TBSP_USED_SIZE in B, NUMBER
System.out.format ("%% used: %3d %%\n", rs.getInt(5)); // TBSP_UTILIZATION in PERCENT
}
tbspname=rs.getString (1);
dfiles_total=rs.getInt(2);
bytes_total=rs.getLong(3);
bytes_used=rs.getLong(4);
percent_used=rs.getInt(5);
}
rs.close () ;
statement.close () ;
connection.close () ;
} catch (java.sql.SQLException e) {
System.err.println (e) ;
System.exit (3) ; // Unknown
}
perfdata = "bytes_used=" + bytes_used + ";; percent_used=" + percent_used + ";; datafiles=" + dfiles_total;
output = tbspname + " " + percent_used + "% used (" + bytesToHuman(bytes_used) + "/" + bytesToHuman(bytes_total)+ ")|" + perfdata;
if ( (bytes_warn != 0) && (bytes_crit != 0) ) {
if ( bytes_used < bytes_warn ) {
System.out.println("OK - " + output);
System.exit (0); // OK
}
if ( bytes_used >= bytes_warn && bytes_used < bytes_crit ) {
System.out.println("WARN - " + output);
System.exit (1); // WARN
}
if ( bytes_used >= bytes_crit ) {
System.out.println("CRIT - " + output);
System.exit (2); // CRIT
}
}
if (args.length == 7 && args[5].equals("-r")) {
System.out.println("OK - " + output);
System.exit (0); // OK
}
}
}