Wednesday, February 29, 2012

Populating Oracle v$session in Spring web app

Our project heavily relies on Oracle PL/SQL procedures. Those procedures are used by different applications and database developers always wanted to know two things:

1) Which application is calling the procedure
2) Who is currently logged into the application

After investigating the topic a bit, I've found OracleConnection.setEndToEndMetrics method in oracle JDBC driver. Using this method, you can populate some fields in v$session view, including v$session.client_identifier and v$session.module. In our case, logged in user goes to client_identifier and calling application to module.

There are already some samples of setting client identifier using this method, but I found most of them incomplete. Here comes another one:



You can see here, that we are using AOP to intercept javax.sql.DataSource.getConnection() methods and populate all connections with logged in user from Spring security SecurityContext. Module is just a constant.