Having a connection pool that connects to a schema with one username. The end users are authenticated to your application. It is possible to get the end user identifier information visible to database level v$session client_identifier column. DBMS_SESSION.SET_IDENTIFIER may be used or with JDBC setEndToEndMetrics method of the oracle.jdbc.OracleConnection.

After setting client identifier information to a session it is possible to set database tracing to a client identifier. But how about your own code, how is it possible to read the client identifier information set in the session? There is no DBMS_SESSION.GET_IDENTIFIER method. One might use select to v$session view but there might be not grants to do so.

Another possibility might be to user DBMS_APPLICATION_INFO.SET_CLIENT_INFO. DBMS_APPLICATION_INFO.READ_CLIENT_INFO is available. Setting client info is not available through JDBC EndToEndMetrics. Client info changes are possible to be set so the client identifier changes also. Set ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE'; This way the tracing by the end user becomes available.

The client identifier is available through SYS_CONTEXT so the GET_IDENTIFIER may be achieved using
select SYS_CONTEXT('userenv', 'CLIENT_IDENTIFIER') from dual;

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.