Re: trace every SQL statement of a specific user

On Mar 5, 2:04 pm, Inquirer <sdama..._at_gmail.com> wrote:
> Hello,
> I've got an application that during its workflow starts a few
> different sessions
> (all running under the same user) and I would rather avoid turning the
> tracing in the
> sessions as it would avoid quite a lot of code changes.
>
> is there a way to trace every sql statement issued by a user,
> independent of what
> and how many sessions she uses?
> Thank you in advance!

Certainly, and it hinges on creating or modifying a login.sql script on the client computer, presuming the user is connecting directly to the database from her desktop (not using a Citrix server or some other application server to connect).

A simple:

alter session set events = '10046 trace name context forever, level 12';

in a login.sql file located in the same directory from which the connecting application runs will enable full tracing on every session she initiates from her desktop. For example:

Presuming the user is connecting directly to the database using an application found locally in c:\myapp\myconn creating a login.sql file containing the above statement in c:\myapp/myconn will enable full session tracing every time this application connects to the database. The trace files will be located on the database server, usually in the directory $ORACLE_BASE/admin/$ORACLE_SID/udump. You can make these dump files easier to find by also setting tracefile_identifier. The login.sql would then be

alter session set tracefile_identifier = 'suspect_user_trace'; alter session set events = '10046 trace name context forever, level 12';

and would put the text 'suspect_user_trace' in the tracefile name.

No other sessions will be affected.

As stated earlier this will not work if she's connecting to the database through an application server.

David Fitzjarrell Received on Wed Mar 05 2008 - 14:42:01 CST