Identify your JDBC Connection in v$session

A major problem for DBA's is the fact that in the v$session view it's really hard to identify which session comes from which J2EE Application.
A select over the v$session view just shows the connected users and from which machine the connect is established, but we can't see from which J2EE Application the sessions are coming :-(

With Oracle AS 10.1.3.x we have the possibility to add a property in the data-source.xml, that is displayed in the column "PROGRAM" of the v$session view.

Following changes have to be done at the data-source.xml configuration file:

1. Check if the factory class oracle.jdbc.driver.OracleDriver is used
2. Add the line inside the connection-factory
3. After the changes, restart your OC4J Instance


<data-sources xsi="http://www.w3.org/2001/XMLSchema-instance" nonamespaceschemalocation="http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd" version="10">

<managed-data-source name="conn_pool_hr_app">

<managed-data-source name="conn_pool_hr_app_reporting">

<connection-pool name="conn_pool_hr_app" limit="3"
connections="3">

<connection-factory class="oracle.jdbc.driver.OracleDriver" user="scott" password="tiger" url="jdbc:oracle:thin:@//localhost:1521/PROD"> <property name="v$session.program" value="conn_pool_hr_app">
</property>
</connection-factory>
</connection-pool>

<connection-pool name="conn_pool_hr_app_reporting" limit="3" connections="3">
<connection-factory class="oracle.jdbc.driver.OracleDriver" user="scott" password="tiger" url="jdbc:oracle:thin:@//localhost:1521/PROD">
<property name="v$session.program" value="conn_pool_hr_app_reporting"></property>
</connection-factory>
</connection-pool>
</data-sources>


If we now select over our v$session view, we can see which session comes from which J2EE Application :-)