This document will explain a procedure that may be used to associate a specific Oracle database session from the V$SESSION table with a specific Siebel user session. This is typically done to troubleshoot specific issues (for example a database process that is using unusually high resources) or to support levels of user auditing not currently available through the Siebel user interface.
The need for this kind of procedure is especially important if using LDAP or Active Directory (ADSI) authentication with shared database credentials. When implementing the LDAPSecAdpt or ADSISecAdpt it is common to used a single shared database account for all Siebel users. This greatly reduces the amount of administrative overhead since you do not have to maintain accounts in the Siebel application, the external directory, and the database server.
The problem with using shared database credentials, however, is that all Siebel user sessions appear as the same user at the database level. This makes it difficult — if not impossible — to match a specific database session with a specific Siebel user session.
Although less of an issue when database authentication is being used with the DBSecAdpt, there are some instances when this procedure might still be helpful. In particular, situations where the same user has logged in multiple times or where a shared userID is being utilized by an outside system through Siebel Enterprise Application Integration (EAI).
Scope
This reference document is intended for system administrators and database administrators working with the Siebel application and Siebel OLTP database. Individuals attempting to utilize this procedure must have adequate rights on both the Oracle database server and the Siebel application. Furthermore, basic Oracle SQL knowledge is required.
Determining which Oracle database session is being used by a specific Siebel user session
IMPORTANT — Please note that the following procedure is only applicable to Siebel implementations using an Oracle database server for their Siebel OLTP database. Furthermore, the Siebel Application Server(s) must be running on Microsoft Windows due to limitations on how the Oracle database client writes information to the V$SESSION table from non-Windows servers. Note that this restriction applies to the Siebel Application Server(s) and not to the Oracle database server which can be on any supported operating system.
In order to associate the Oracle database session with the Siebel user session, you need to use the a combination of SQL*Plus, the session server management screen, and the application object manager logs. The application manager logs provide the key link. If you look at the very beginning of an application object manager log, you will see something similar to:
2021 2009-07-24 14:15:28 0000-00-00 00:00:00 -0600 00000000 001 003f 0001 09 SSEObjMgr_enu 5207 3948 1356
The last three sets of numbers are the ones we care about.
The first (5207 in this case) is the Siebel Session ID.
The second (3948 in this case) is the Server PID.
The third (1356) in this case is the Thread ID.
You can use the Site Map > Administration – Server Management > Sessions view to find the Session ID and then look at the OM login field to see what user is logged into that session.
Alternatively use the srvrmgr command - list sessions for comp SCCObjMgr_enu . - and compare the task id to determine the user.
On the Oracle database side, you can query the V$SESSION table (make sure you are logged in as the tableowner) using the following WHERE clause format:
WHERE PROCESS=’XXXX:YYYY’
XXXX = Server PID
YYYY = Thread ID
For example, the following SQL query (using the specific values from above) would return the most commonly requested information:
select USERNAME, STATUS, TYPE, TERMINAL, PROGRAM, LOGON_TIME
from v$session where PROCESS='3948:1356';
You can also do the process in reverse by examining the PROCESS field in V$SESSION and then using the returned Server PID and Thread ID to find the Siebel Session ID in the application object manager logs.