Monday, 24 May 2010

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();
}

No comments: