Monitoring the disk space from inside Oracle

Monitoring the disk space using a UNIX script is quite a simple task, whereas doing the same from PL/SQL is more entertaining.

This kind of function can be implemented using a Java class and be accessed by PL/SQL.

In the class a public function listDf is defined, which executes the operating system command /usr/bin/df -k; the output of the command is stored in a string and returned to the PL/SQL code.


//======================================================================================
public class Cdf {
    
  public Cdf() {
  }

  public static String listDf() {
    String sLs     = "";
    String s       = "";
    
    try
    {
      sLs = "";
      boolean found = false;

      Process p = Runtime.getRuntime().exec("/usr/bin/df -k");
      InputStream is = p.getInputStream();
      DataInputStream dis = new DataInputStream(is);
      do{
        s = dis.readLine();
        if(s != null) {
          //System.out.println("ls returns: " + s + "\n");
          sLs = sLs + s + "\n";
	    }
      }while (s!= null);

    }
    catch(IOException io) {
      sLs = "There was an error";
    }
    return sLs;   
  }
}

The first step is to compile the class and load it into the database:

oracle:> loadjava -user sys/syspwd -oci8 -resolve Cdf.class

A PL/SQL wrapper is created around the Java class:

CREATE OR REPLACE FUNCTION listDf
   RETURN VARCHAR2
AS LANGUAGE JAVA
  NAME 'Cdf.listDf() return java.lang.String';

The function is called from PL/SQL

SQL> set heading off pagesize 0
SQL> select listDf from dual;
Filesystem            kbytes    used   avail capacity  Mounted on
/dev/md/dsk/d0       2508555 1355119 1103265    56%    /
/proc                      0       0       0     0%    /proc
fd                         0       0       0     0%    /dev/fd
mnttab                     0       0       0     0%    /etc/mnttab
/dev/md/dsk/d40      3009327 2102602  846539    72%    /var
swap                 12753856      16 12753840     1%    /var/run
swap                 13060144  306304 12753840     3%    /tmp
/dev/vx/dsk/feeddg/usr1 11796416 7027340 3589435    67%    /usr1
/dev/vx/dsk/cspdg/usr2 5898200 2361593 2946787    45%    /usr2
/dev/vx/dsk/cspdg/usr3 7864272 3967507 3110338    57%    /usr3
/dev/vx/dsk/cspdg/usr4 1463018   35138 1281579     3%    /usr4
/dev/vx/dsk/cspdg/usr5 1966056  531238 1238213    31%    /usr5
/dev/vx/dsk/cspdg/usr6 3932128 1886410 1652506    54%    /usr6
/dev/vx/dsk/cspdg/u01 13762488 9823075 2563165    80%    /u01
/dev/vx/dsk/cspdg/u03 5898200 3581787 1726593    68%    /u03
/dev/vx/dsk/cspdg/u05 3932128 2033419 1505497    58%    /u05
/dev/vx/dsk/cspdg/u02 12779452 8737348 2764159    76%    /u02
/dev/vx/dsk/cspdg/u08 4915164 3303035 1120613    75%    /u08
/dev/vx/dsk/cspdg/u09 9830344 5240920 3606390    60%    /u09
/dev/vx/dsk/cspdg/u04 3932128 2501204 1037712    71%    /u04
/dev/vx/dsk/cspdg/u06 4915164 2027323 2396325    46%    /u06
/dev/vx/dsk/cspdg/u10 9830344 3219737 5627573    37%    /u10
/dev/md/dsk/d20      5136174 3046622 2038191    60%    /opt
/dev/md/dsk/d30      1987399 1350971  576807    71%    /export/home

The following PL/SQL code filters the output of the df command, extracting only the mount points that contain /u and their capacity. It can be changed according to your needs.

DECLARE
  sDf       VARCHAR2(24000);
  sBuf      VARCHAR2(2000);
  i1        INTEGER := 0;
  i2        INTEGER := 0;
  ip        INTEGER := 0;
  pctF      INTEGER ;
  ipu       INTEGER := 0;
  sMP       VARCHAR2(240);
  c         sys.utl_smtp.connection;
  MIN_SPACE INTEGER := 25;
  message   VARCHAR2(254);
BEGIN
  sDf := sys.listDf;
 dbms_output.put_line('the output length is '||TO_CHAR(LENGTH(sDf)));
 FOR i in 1..100
 LOOP
   i1 := INSTR(sDf,'/dev',1,i);
   if (i1 <> 0) THEN
     --dbms_output.put_line('dev found at position '||TO_CHAR(i1));
     if (i2 <> 0) THEN
       sBuf := SUBSTR(sDf,i2, i1 -i2);
       --dbms_output.put_line(sBuf);
       ip := INSTR(sBuf,'%',1,1);
       ipu := INSTR(sBuf,'/u',1,1);
       IF(ip > 0 AND ipu> 0 ) THEN
         dbms_output.put_line(SUBSTR(sBuf, ip + 1, i1 -i2 -ip -1)||
		 ' '||SUBSTR(sBuf, ip - 3, 3));
         sMP := SUBSTR(sBuf, ip + 1, i1 -i2 -ip -1);
         pctF := 100. - TO_NUMBER(SUBSTR(sBuf, ip - 3, 3));
         IF ( pctF  < 25 ) THEN
           dbms_output.put_line('Chiedo un collegamento   ');
           c  := utl_smtp.open_connection('mail.front-row-seat.com');
           utl_smtp.helo(c, 'front-row-seat.com');
           utl_smtp.mail(c, 'oracle@mydb.com');
           utl_smtp.rcpt(c, 'support@front-row-seat.com');
           utl_smtp.open_data(c);
           utl_smtp.write_data(c,'Subject' || ':' || sMP ||
		   ' is is low in space: only '||TO_CHAR(pctF)||'% left');
           message :=  sMP ||' is is low in space';
           utl_smtp.write_data(c,utl_tcp.CRLF || message);
           utl_smtp.close_data(c);
           utl_smtp.quit(c);
         END IF;

       END IF;
     END IF;
   END IF;
   i2 := i1;
 END LOOP;
end;
/

Here is the sample output

SQL> set serverout on size 1000000
SQL> @dfFromOracle.sql
the output length is 1644
/usr1  69
/usr2  43
/usr3  57
/usr4   3
/usr5  31
/usr6  54
/u01  80
/u03  68
/u05  58
/u02  76
/u08  75
/u09  68
/u04  71
/u06  46
/u10  37

PL/SQL procedure successfully completed.

If the space is lower than the threshold MIN_SPACE, an email is sent using the utl_smtp package.

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good