Re: trace every SQL statement of a specific user

On Mar 6, 1:42 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

if you were using oracle 10g then please check the option dbms_monitor package and trcsess command. I think these two servers ur purpose.

Uday Received on Thu Mar 06 2008 - 05:15:17 CST