Working Hard

Been no tips cause there is too much work. Other than this one... recently had the need to match a java.sql.Connection to its Oracle counterpart. We did it with a SID & Serial.

If you execute this query as you take a connection from the pool, you can match them up. Not a good idea in production, but ok for test and debug.

select sid, serial#, metauser from v$session where audsid = userenv('sessionid')

Pair that up with the toString() result of your connection and you have a pretty decent way to figure out what isn't getting closed. Oh yeah, use some dummy exception logic like this to print how each connection handled. This is definitely NOT for use in production.


protected String getStackTrace() {
StringBuffer stack = new StringBuffer();
try {
throw new Exception("dummy");
} catch(Exception e) {
StackTraceElement[] st = e.getStackTrace();
for(int i=0;i < st.length;i++) {
stack.append(st[i].toString()).append("\r\n");
}
}
return stack.toString();
}



Connection leaks do suck. Oh, metauser isn't an oracle function, it's one of ours. But it also has a purpose in this connection pool world. The metauser is established when a connection is given out based on the user identity, since the Oracle user function will return the id for the pool. We call metauser & store the value in some session level package variable. We use metauser in stored procedures (which we have loads of) instead of user. I hear there is a way around this in Oracle, but not sure how to do it. Ideas welcome.

Enjoy.