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

advertisements

_____________________________________________________________________________________________________________________

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"

 as

 import java.io.*;

 public class ServerDet {

 public static String getInfo ()

 throws Exception {

 Runtime r = Runtime.getRuntime();

 Process p = r.exec("hostname");

 BufferedReader br

 = new BufferedReader(new

InputStreamReader(p.getInputStream()));

 p.waitFor();

 String a;

 StringBuffer sb = new StringBuffer();

 while ((a = br.readLine()) != null) {

 sb.append(a + "\n");

 }

 br.close();

Runtime r1 = Runtime.getRuntime();

 Process q = r1.exec("cat /proc/meminfo");

 BufferedReader qr

 = new BufferedReader(new

InputStreamReader(q.getInputStream()));

 q.waitFor();

 while ((a = qr.readLine()) != null) {

 sb.append(a + "\n");

 }

 qr.close();

Runtime r2 = Runtime.getRuntime();

 Process s = r1.exec("cat /proc/cpuinfo");

 BufferedReader sr

 = new BufferedReader(new

InputStreamReader(s.getInputStream()));

 s.waitFor();

 while ((a = sr.readLine()) != null) {

 sb.append(a + "\n");

 }

 sr.close();

 return sb.toString();

 }

 }

/

Java created.

Step 2 Create a function to call Java source.

create or replace function OSServerDet return varchar2

is

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;

devqa.testlab.ktl

MemTotal:      6291456 kB

MemFree:        239880 kB

Buffers:        211656 kB

.. .. ..

.. .. ..

address sizes   : 40 bits physical, 48 bits virtual

power management:

How is it?

_____________________________________________________________________________________________________________________

Website Stats