Since the key is to identify Oracle server processes of interest, you need to investigate how the session identification columns are set for your application. You may be surprised by what you see, so run a simple query and take a look. Figure 5-19 is the result of such a query run on a real PeopleSoft system. While not shown because DBMS_MONITOR does not directly filter on this column, the client_info column provides a wealth of information about the PeopleSoft session. Notice that the application and the DBA have not set the client identifier column, so unless the DBA takes action to set this column, it will remain empty. By closely examining such a query on your system, you will begin to understand your session identification options.

One of the most powerful ways to identify a session is based on its client identifier. This column resides in v$session and can be set by the dbms_session.set_identifier procedure. Increasingly, applications are setting the client identifier in addition to the module and action.

If sessions are not persistent (that is, they connect, run SQL, and disconnect), then combining a logon trigger with setting the client identifier may enable you to pinpoint a specific user or group of users. Figure 5-20 shows a working logon trigger used for just such an occasion.