Identify the connected program to an Oracle databaseTag(s): JDBC


The v$session view contains session information for each current session.

When connecting with ODBC or OCI, the program column will contain the executable name used to make the connection. If the connection is made with the Thin Driver,the program is "JDBC Thin Client" (by default). The good news is that this information can be customized at the connect time to provide a more interesting name (maximum length = 64 characters).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;

public class TestOra5 {
  public TestOra5() {    }

  public void doit () throws SQLException {
    Properties props = new Properties();

    props.setProperty("user","scott");
    props.setProperty("password","tiger");

    props.setProperty(
       OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_PROGRAM,
       "My Java program : " + this.getClass().getName() );

    String url = "jdbc:oracle:thin:@//oracle.mycompany.com:5561/myinstance";
    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);

    String sql =
       "SELECT username, osuser, program, machine " +
       "FROM SYS.V_$SESSION " +
       "WHERE username IS NOT null ORDER BY logon_time, sid";

    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    String format = "|%1$-20s|%2$-20s|%3$-40s|%4$-20s\n";
    System.out.format(format, "username", "osuser.", "program", "machine");
    while (rs.next())
       System.out.format
         ( format, rs.getString(1), rs.getString(2),
           rs.getString(3), rs.getString(4));
    conn.close();
  }

  public static void main(String[] args){
    TestOra5 test = new TestOra5();
    try {
       test.doit();
       System.out.println("Done..");
    }
    catch (SQLException e) {
       e.printStackTrace();
    }
  }
}
Output :
|username            |osuser.             |program                              |machine
|bigadmin            |real                |JDBC Thin Client                     |Jupiter
|real                |real                |Winsql.exe                           |Saturn
|scott               |real                |My Java program : TestOra5           |Mercury

blog comments powered by Disqus