Re: disk space trending into table....

oracle_man wrote:
> Does any person out there have a package that grabs disk space usage
> via df -k or -g or whatever and stuff it into an oracle table?
>
> Sure could use one.
>
> Thanks,
>
> Rich
>

I did this with Java in the database. See the following:

--
-- create_df_java.sql
-- by Brian Peasland
-- 06 January 2003
--
-- This script is used to create a Java Stored Procedure
-- to execute the "df -k" host command, from within the
-- the database. The results of the command will be placed
-- into a table for later processing.
--

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OS_Util"
AS

import java.io.*;
import java.lang.*;
import oracle.jdbc.*;
import java.sql.*;

public class OS_Util extends Object
{

    public static int df ( String[] arg ) throws SQLException
    {
       //
       // Variable Declarations
       //
       Runtime rt = Runtime.getRuntime();
       int rc = -1;
       String inputLine=null;
       String sql;
       PreparedStatement pstmt;

       //Open a database connection
       //Since this is a Java Stored Procedure and runs in the database,
       //this connection just uses the default connection.

       Connection conn =
          DriverManager.getConnection("jdbc:default:connection:");
       //Clear the table to start anew
       sql = "DELETE FROM infoctr.df_temp";
       pstmt = conn.prepareStatement(sql);
       pstmt.executeUpdate();

       // Run the 'df -k' command and put the output
       // into the table
       try {
          Process p = rt.exec("/usr/bin/df -k");

          InputStreamReader in = new InputStreamReader(p.getInputStream());
          BufferedReader inStream = new BufferedReader(in);


          //Place results of "df -k" into a table
          while ((inputLine = inStream.readLine()) != null ) {

             //Insert data into database table
             sql = "INSERT INTO infoctr.df_temp VALUES (?)";
             try {
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, inputLine);
                pstmt.executeUpdate();
                } //try
             catch (SQLException e) {
                System.err.println(e.getMessage());
                } //catch

             } //while

          //Close database connection
          conn.close();

          rc = p.waitFor();
          } //try
       catch (Exception e) {
          e.printStackTrace();
          rc = -1;
          } //catch
       finally {
          return rc;
          } //finally
    } //df

} //Util
/

I then created a wrapper function to call the Java code:

CREATE OR REPLACE FUNCTION df (arg IN VARCHAR2)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'OS_Util.df(java.lang.String[]) return int';
/


The above could easily be a stored procedure too.


HTH,
Brian


-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown