How to Set v$session.program From a Universal Connection Pool (UCP)

The follow shows how you can ensure connections created from your Oracle Universal Connection Pool (UCP) clients can be uniquely identified using the Oracle JDBC driver property v$session.program. The property is an Oracle JDBC driver property so what we do here is the following.

1. Set the factory class to "oracle.jdbc.OracleDriver" using the method PoolDataSource.setConnectionFactoryClassName()
2. Use the PoolDataSource.setConnectionFactoryProperties() method to specify the driver properties that each Connection will use.

So here is a basic class showing how to set v$session.program and verify it did this correctly from SQL*Plus.


TestUCPJDBCProps.java

package demo;

import java.io.IOException;

import java.sql.Connection;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import oracle.ucp.UniversalConnectionPoolAdapter;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.admin.UniversalConnectionPoolManager;
import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;


public class TestUCPJDBCProps
{
  private PoolDataSource pds = null;
  private Properties props = new Properties();
  private UniversalConnectionPoolManager mgr = null;
  private String poolName = "PasUCPTest";
  
  public TestUCPJDBCProps() throws SQLException, UniversalConnectionPoolException
  {  
    mgr = UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager();
    
   // Create pool-enabled data source instance.
    pds = PoolDataSourceFactory.getPoolDataSource();
    // PoolDataSource and UCP configuration
    
    //set the connection properties on the data source and pool properties
    pds.setUser("scott");
    pds.setPassword("tiger");
    pds.setURL("jdbc:oracle:thin:@//beast.au.oracle.com:1523/linux11gr2");
    pds.setConnectionFactoryClassName("oracle.jdbc.OracleDriver");
    pds.setInitialPoolSize(2);
    pds.setMinPoolSize(2);
    pds.setMaxPoolSize(20);
    pds.setConnectionPoolName(poolName);
    props.put("v$session.program", "scott-ucp-j2seclient");
    pds.setConnectionFactoryProperties(props);
    
    mgr.createConnectionPool((UniversalConnectionPoolAdapter)pds);
    
    mgr.startConnectionPool(poolName);

  }

  public void run () throws SQLException, IOException
  {
    List connList = new ArrayList();
    
    for (int i = 0; i < 5 ;i++ ) 
    {
      //Get a database connection from the datasource. 
      Connection conn = pds.getConnection();
      System.out.println("Retrieved a connection from pool");
      connList.add(conn);
    }

    System.out.println("Press Enter to finish the demo -> ");
    System.in.read();
      
    // close all connections
    for (int j = 0; j < connList.size() ; j++) 
    {
      ((Connection)connList.get(j)).close();
    }
    
  }
  
  public void stopPool () throws UniversalConnectionPoolException
  {
    mgr.stopConnectionPool(poolName);  
  }
  
  public static void main(String[] args) throws IOException
  {
    System.out.println("Started UCP JDBC Property Test at " + new Date());
    TestUCPJDBCProps test;

    try
    {
      test = new TestUCPJDBCProps();
      test.run();
      test.stopPool();
    }
    catch (Exception e)
    {
      e.printStackTrace();
      System.exit(-1);
    }
    
    System.out.println("Ended UCP JDBC Property Test at " + new Date());
  }
} 

SQL*PLus Output

SCOTT@linux11gr2> @query-scott.sql

SCOTT sessions

USERNAME PROGRAM                   STATUS
-------- ------------------------- --------
SCOTT    sqlplus.exe               ACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE

6 rows selected.

SCOTT@linux11gr2>

The SQL for the query above was as follows.

set head on feedback on

set pages 999
set linesize 120

prompt
prompt SCOTT sessions

col machine format a25
col username format a15
col username format a8
col program format a25

select
username,
program,
status,
last_call_et seconds_since_active,
to_char(logon_time, 'dd-MON-yyyy HH24:MI:SS') "Logon"
from v$session
where username = 'SCOTT'
/