Re: tracking a session using connection pooling

i_rahim_at_hotmail.com wrote:
> Hi, does anyone know how I can determine the terminal, osuser, program
> from a session that has been created from a connection pool from jvm ?

A V$SESSION row is created for every single session/connection to Oracle. Irrespective of whether the client is TOAD, another Oracle Database or a JVM process from a connection pool on an app server.

The V$SESSION shows the information that the client driver (OCIlib, Thin JDBC) passed to the Listener - including the process image name of the client that is making the call, the terminal on which the client process runs, and the o/s username/account in which that process runs.

Oracle does not know if this data is correct, spoofed, painted pinked or delivered in blue bytes. It is simply data that the client driver used as part of its connection string to Listener - data which the Listener in turned passed on to the Oracle Dedicated Server or Oracle Dispatcher.

Nothing more.

> There is obviously a route back to the terminal but v$session will show
> only limited information due to the connection pooling.

Connection pooling does not cause limited information to be displayed. What is displayed ito terminal, process image name, o/s user and so on is directly dependant on what the client driver supplied in its connection string.

The sizes of the actual columns in V$SESSION is restricted to the actual C structures for that session that is kept in memory (called the UGA). So if the question is how do you know who made the web/whatever connection to the web/app server that in turn caused the pooled app server connection to Oracle to be used.. that is not Oracle's problem. It is not a user that has made the connection. It is the app server. Oracle has not the foggiest notion that the connection is actually an app server connection pool process that in turn services user on another system/platform somewhere. And nor does it care. That's the app server's problem. Not Oracle's.

--
Billy