Unlocking Session Visibility with Oracle Client Info

Monitoring database interactions is critical for performance tuning, debugging, and security. With Oracle AI Database, you can attach client metadata to connections; such as client identifiers, module and action names. Client metadata can be queried from views like V$SESSION to see where your application is interacting with the database.

In this article, we’ll build a sample Spring Boot application that integrates client info into database connections. The sample includes a REST API for managing books that shows both global and per-operation client info settings.

Prerequisites:

Java 21+
Maven
Docker-compatible environment (for docker-compose)

You can view the full sample app here: Spring Boot Database Client Info.

Project Dependencies

We use Spring starters for JDBC and web interfaces, as well as the Oracle Spring Boot starter for UCP (Universal Connection Pool):

<dependencies>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
  </dependency>
  <dependency>
      <groupId>com.oracle.database.spring</groupId>
      <artifactId>oracle-spring-boot-starter-ucp</artifactId>
      <version>${oracle.starters.version}</version>
  </dependency>

  <!-- Include if using Oracle Database Wallet -->
<!--        <dependency>-->
<!--            <groupId>com.oracle.database.spring</groupId>-->
<!--            <artifactId>oracle-spring-boot-starter-wallet</artifactId>-->
<!--            <version>${oracle.starters.version}</version>-->
<!--        </dependency>-->
</dependencies>

<dependencies>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
  </dependency>
  <dependency>
      <groupId>com.oracle.database.spring</groupId>
      <artifactId>oracle-spring-boot-starter-ucp</artifactId>
      <version>${oracle.starters.version}</version>
  </dependency>

  <!-- Include if using Oracle Database Wallet -->
<!--        <dependency>-->
<!--            <groupId>com.oracle.database.spring</groupId>-->
<!--            <artifactId>oracle-spring-boot-starter-wallet</artifactId>-->
<!--            <version>${oracle.starters.version}</version>-->
<!--        </dependency>-->
</dependencies>

In the Spring application properties, we configure the Java DataSource to use UCP:

spring:
  application:
    name: MyApp
  datasource:
    username: testuser
    password: testpwd
    url: jdbc:oracle:thin:@localhost:1525/freepdb1
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSource
    oracleucp:
      initial-pool-size: 1
      min-pool-size: 1
      max-pool-size: 30
      connection-pool-name: ${spring.application.name}
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource

spring:
  application:
    name: MyApp
  datasource:
    username: testuser
    password: testpwd
    url: jdbc:oracle:thin:@localhost:1525/freepdb1
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSource
    oracleucp:
      initial-pool-size: 1
      min-pool-size: 1
      max-pool-size: 30
      connection-pool-name: ${spring.application.name}
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource

Set app and host name

To inject global database client info we wrap a Java DataSource with a Properties object (ClientInfoDataSource):

package com.example.clientinfo;

import org.springframework.jdbc.datasource.DelegatingDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ClientInfoDataSource extends DelegatingDataSource {
    private final Properties clientInfo;

    public ClientInfoDataSource(DataSource original, Properties clientInfo) {
        super(original);
        this.clientInfo = clientInfo;
    }

    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = super.getConnection();
        connection.setClientInfo(clientInfo);
        return connection;
    }
}

package com.example.clientinfo;

import org.springframework.jdbc.datasource.DelegatingDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ClientInfoDataSource extends DelegatingDataSource {
    private final Properties clientInfo;

    public ClientInfoDataSource(DataSource original, Properties clientInfo) {
        super(original);
        this.clientInfo = clientInfo;
    }

    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = super.getConnection();
        connection.setClientInfo(clientInfo);
        return connection;
    }
}

Then, we configure the properties object with the app and host name using the OCSID.CLIENTID property (Configuration class):

@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
    InetAddress address;

    try {
        address = InetAddress.getLocalHost();
    } catch (UnknownHostException e) {
        throw new RuntimeException(e);
    }
    if(bean instanceof DataSource ds) {
        Properties props = new Properties();
        String clientId = "%s@%s".formatted(appName, address.getHostName());
        props.setProperty("OCSID.CLIENTID", clientId);
        return new ClientInfoDataSource(ds, props);

    }
    return BeanPostProcessor.super.postProcessAfterInitialization(bean, beanName);
}

@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
    InetAddress address;

    try {
        address = InetAddress.getLocalHost();
    } catch (UnknownHostException e) {
        throw new RuntimeException(e);
    }
    if(bean instanceof DataSource ds) {
        Properties props = new Properties();
        String clientId = "%s@%s".formatted(appName, address.getHostName());
        props.setProperty("OCSID.CLIENTID", clientId);
        return new ClientInfoDataSource(ds, props);

    }
    return BeanPostProcessor.super.postProcessAfterInitialization(bean, beanName);
}

Now, each DataSource connection uses the OCSID.CLIENTID=MyApp@<hostname> property. You can extend this for any global client info.

Set module and action for a REST controller

The BooksController exposes a CRUD API for books. In each operation, we configure the OCSID.MODULE and OCSID.ACTION client info properties:

private Connection getConnectionWithClientInfo(String action) throws SQLException {
    Connection conn = dataSource.getConnection();
    conn.setClientInfo("OCSID.MODULE", "Books");
    conn.setClientInfo("OCSID.ACTION", action);
    return conn;
}
```
// Example usage in getAllBooks():
try (Connection conn = getConnectionWithClientInfo("getAllBooks");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM books")) {
    // ...
}

private Connection getConnectionWithClientInfo(String action) throws SQLException {
    Connection conn = dataSource.getConnection();
    conn.setClientInfo("OCSID.MODULE", "Books");
    conn.setClientInfo("OCSID.ACTION", action);
    return conn;
}
```
// Example usage in getAllBooks():
try (Connection conn = getConnectionWithClientInfo("getAllBooks");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM books")) {
    // ...
}

The BooksController uses similar code for POST, GET, and DELETE methods.

Run the app

Clone the spring-boot-database-client-info project and start the Oracle Database Free container:

docker compose up -d

docker compose up -d

This runs grant_permissions.sql to create a test user (testuser) and the books table:

create table testuser.books (
    id               number generated always as identity primary key,
    title            varchar2(255) not null,
    author           varchar2(255) not null,
    isbn             varchar2(20),
    published_date   date
);

create table testuser.books (
    id               number generated always as identity primary key,
    title            varchar2(255) not null,
    author           varchar2(255) not null,
    isbn             varchar2(20),
    published_date   date
);

Then run the app, which starts on port 8080:

mvn spring-boot:run

mvn spring-boot:run

Testing client info

Create a book:

curl -X POST http://localhost:8080/books \
  -H "Content-Type: application/json" \
  -d '{"title":"Test Book","author":"John Doe","isbn":"1234567890","publishedDate":"2023-01-01"}'

curl -X POST http://localhost:8080/books \
  -H "Content-Type: application/json" \
  -d '{"title":"Test Book","author":"John Doe","isbn":"1234567890","publishedDate":"2023-01-01"}'

Get all books:

curl http://localhost:8080/books

curl http://localhost:8080/books

Observe client info from the V$SESSION view:

SELECT sid, client_identifier, module, action FROM v$session WHERE username = 'TESTUSER';

SELECT sid, client_identifier, module, action FROM v$session WHERE username = 'TESTUSER';

This shows entries like CLIENT_IDENTIFIER=”MyApp@hostname”, MODULE=”Books”, ACTION=”createBook”:

Database session information table showing SID, CLIENT_IDENTIFIER, MODULE, and ACTION for a Spring Boot application managing books.

You can take this a step further and view historical session data from V$ACTIVE_SESSION_HISTORY:

SELECT 
    ash.sample_time,
    ash.session_state,
    ash.event,
    ash.sql_id
FROM v$active_session_history ash
WHERE ash.MODULE = 'Books'
ORDER BY ash.sample_time DESC;

SELECT 
    ash.sample_time,
    ash.session_state,
    ash.event,
    ash.sql_id
FROM v$active_session_history ash
WHERE ash.MODULE = 'Books'
ORDER BY ash.sample_time DESC;

References

Looking for more observability tools? Check out these resources:

andersswanson.dev