Oracle 10g:Get Unix/Linux Mount Point info /Alert logs in Mail By Java Source
Execute Unix/Linux commands by Java Source
This is very easy with few steps.
1.CREATE JAVA SOURCE into oracle 10g database.
Install jvm.Connect to SQL with sysdba priviledges.
@$ORACLE_HOME/javavm/install/initjvm.sql
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
// Use the appropriate path for your windows version.
//finalCommand[0] = "C:\\winnt\\system32\\cmd.exe"; // Windows NT/2000
finalCommand[0] = "C:\\windows\\system32\\cmd.exe"; // Windows XP/2003
//finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe"; // Windows 64-bit
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr = Runtime.getRuntime().exec(finalCommand);
pr.waitFor();
new Thread(new Runnable(){
public void run() {
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch (Exception ex) {}
}
}
}).start();
new Thread(new Runnable(){
public void run() {
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}
};
/
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
// Use the appropriate path for your windows version.
//finalCommand[0] = "C:\\winnt\\system32\\cmd.exe"; // Windows NT/2000
finalCommand[0] = "C:\\windows\\system32\\cmd.exe"; // Windows XP/2003
//finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe"; // Windows 64-bit
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr = Runtime.getRuntime().exec(finalCommand);
pr.waitFor();
new Thread(new Runnable(){
public void run() {
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch (Exception ex) {}
}
}
}).start();
new Thread(new Runnable(){
public void run() {
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}
};
/
2.CREATE Procedure for executing unix command.
CREATE OR REPLACE PROCEDURE host_command (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
3.Give Privileges to user who wants to execute commads.
DECLARE
l_schema VARCHAR2(30) := 'SYSTEM'; -- Adjust as required.
BEGIN
DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/
l_schema VARCHAR2(30) := 'SYSTEM'; -- Adjust as required.
BEGIN
DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/
4.Test Commands.
SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
host_command (p_command => '/bin/df -h');
END;
/
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
host_command (p_command => '/bin/df -h');
END;
/
5. Create procedure to mail the info
CREATE OR REPLACE PROCEDURE usp_get_mount_point_info
as
l_output DBMS_OUTPUT.chararr;
l_lines INTEGER := 2000;
l_lines INTEGER := 2000;
vmsg varchar2(10000);
begin
vmsg:='<html><br /><br /><br /><table border=1 align="center" ><tr><td colspan=1 align="center"><b>Unix Ssystem Mount point Info</b></td></tr>';
vmsg:=vmsg||'<br /><br /><br /><table border=1 align="center" ><tr><td align="center"><b>Mount Points</b></td></tr>';
DBMS_OUTPUT.enable(1000000);
DBMS_JAVA.set_output(1000000);
host_command('/bin/df -h');
DBMS_OUTPUT.get_lines(l_output, l_lines);
FOR j IN 1 .. l_lines LOOP
--DBMS_OUTPUT.put_line(l_output(i));
--vmsg:=vmsg||'<tr><td>'||l_output(j)||'</td><td></tr>';
vmsg:=vmsg||'<tr><td>'||substr(l_output(j),25,length(l_output(j)))||'</td><td></tr>';
END LOOP;
vmsg:=vmsg||'</table><br /><br /></html>';
UTL_MAIL.SEND(sender=>'***.***@*****.co.in', recipients=>'bhavesh.thakkar@youbroadband.co.in', cc =>'***.***@***.***.co.in' , subject=>'DatabaseName Mount Point Info', message => vmsg, mime_type => 'text/html');
end;
/
6.Schedule this info as per your requirement.
Comments
Post a Comment
Dear User,
Thank you very much for your kind response