Oracle SQL Select Statement / Function to get Server Memory, CPU and Processor details

Once I got a requirement to fetch the server details. Initially I thought of login to each and every box and get the details. But if it is one or ten box it’s okay to get the details manually. But if you think about 100s of servers and also periodic activity,
it will be terrific task. J I thought about ORACLE select statement which can be fired from one single database through TNS. This is not required to connect to each and every box physically. See here how I achieved this.

Step 1 Create a Java Source. This is created for Linux boxes and you can modify according to your OS requirement. Only thing you have to modify the UNIX commands mention in the red color below.

create or replace and compile java source
 named "ServerDet"
 public class ServerDet {

 public static String getInfo ()
 throws Exception {

 Runtime r = Runtime.getRuntime();
 Process p = r.exec("hostname");
 BufferedReader br
 = new BufferedReader(new
 String a;
 StringBuffer sb = new StringBuffer();
 while ((a = br.readLine()) != null) {
 sb.append(a + "\n");

Runtime r1 = Runtime.getRuntime();
 Process q = r1.exec("cat /proc/meminfo");
 BufferedReader qr
 = new BufferedReader(new
 while ((a = qr.readLine()) != null) {
 sb.append(a + "\n");

Runtime r2 = Runtime.getRuntime();
 Process s = r1.exec("cat /proc/cpuinfo");
 BufferedReader sr
 = new BufferedReader(new
 while ((a = sr.readLine()) != null) {
 sb.append(a + "\n");

 return sb.toString();

Java created.

Step 2 Create a function to call Java source.
create or replace function OSServerDet return varchar2
language java name 'ServerDet.getInfo() return java.lang.String';

Function created.

Step 3 Select from the function and spool the output.
set heading off
select OSServerDet from dual;
MemTotal:      6291456 kB
MemFree:        239880 kB
Buffers:        211656 kB
.. .. ..
.. .. ..
address sizes   : 40 bits physical, 48 bits virtual
power management:

How is it?
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google