Re: Turning SQL tracing on for a Java application

From: http://www.webyourcompany.com <xzhang88_at_hotmail.com>
Date: 28 May 2003 21:55:26 -0700
Message-ID: <2f50b64b.0305282055.10b04e63_at_posting.google.com>


I can think of two approaches:

  1. use logon trigger

You can define a new connection pool whose users you always want to trace

create trigger sql_trace after logon on database begin

   if ( user = 'USER YOU WANT TO TRACE' )    then
     execute immediate 'alter session set sql_trace=true';    end if;
end;
/

2. Change the getConnection(), and connectino.close()

In the getConnection(), you check (property file or config file) to determine if you want to turn on trace.... then in the conn.close() method you diable the trace if you want.

In the getConnection() method, before you return the conn to the caller, you use that conn to run a stmt.execute("alter session set sql_trace=true");

Hope this helps.
http://www.webyourcompany.com

robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0305281146.31db17d6_at_posting.google.com>...
> What is the best way to turn on Oracle sql tracing on for a specific
> application. I know you can set instance wide sql tracing on but that
> is not very convenient because it traces every application that uses
> that instance not just the one I am interested in. I could do
>
> EXECUTE SYS.dbms_system.set_sql_trace_in_session (sid, ...
>
> for a particular session but that means I have to do it session by
> session which is not practical since the application pools connections
> and new sessions get created and closed as the pool grows/shrinks.
>
> Is there any way to turn on tracing for all sessions of one specific
> application?
>
> Can the application itself invoke ALTER SESSION on every connection it
> creates to achieve this?
>
> Thanks!
Received on Thu May 29 2003 - 06:55:26 CEST

Original text of this message