DBMS_MONITOR for Tracing Current Session from Oracle JDBC

Note for myself.

Turn On

begin DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => ?, serial_num => ?, waits => TRUE, binds => TRUE); end;

Turn Off

begin DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id => ?, serial_num => ?); end;

In order to get the session_id and serial_num use a query as follows to dynamically determine that. This query can be used from Oracle 9i onwards

SCOTT@linux11gr2> SELECT dbms_debug_jdwp.current_session_id sid,
2  dbms_debug_jdwp.current_session_serial serial#
3  from dual;

    SID    SERIAL#
---------- ----------
    411       5395

SCOTT@linux11gr2>

JDBC Code as follows

  
  // hold session info for connected user
  private int serialNum;
  private int sessionId;

  private void turnOnSessionTrace (Connection conn) throws SQLException
  {
    String sql = 
      "SELECT dbms_debug_jdwp.current_session_id sid, " + 
      "dbms_debug_jdwp.current_session_serial serial#  " +
      "from dual";  
    String turnOnSQL = 
      "begin DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => ?, " + 
      "serial_num => ?, waits => TRUE, binds => TRUE); end;";

    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    rset.next();
    
    if (rset != null)
    {
      sessionId = rset.getInt(1);
      serialNum = rset.getInt(2);
    }
    
    System.out.println(
      String.format("Session Details : session_id=%s, serial_num=%s", 
                    sessionId, serialNum));
    
    OracleCallableStatement csmt = (OracleCallableStatement) conn.prepareCall(turnOnSQL);
    csmt.setInt(1, sessionId);
    csmt.setInt(2, serialNum);
    csmt.execute();
    
    stmt.close();
    rset.close();
    csmt.close();
  }

  private void turnOffSessionTrace (Connection conn) throws SQLException
  {
    String turnOffSQL = 
      "begin DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id => ?, serial_num => ?); end;";
    
    OracleCallableStatement csmt = (OracleCallableStatement) conn.prepareCall(turnOffSQL);
    csmt.setInt(1, sessionId);
    csmt.setInt(2, serialNum);
    csmt.execute();   
  }