Re: trace filename

From: A. Coskan Gundogar <gundogar_at_gmail.com>
Date: Wed, 21 Mar 2007 12:56:49 +0200
Message-ID: <d1f742de0703210356w30ab4e9eua2f332d38b1137ac@mail.gmail.com>

Not the real solution but may so close to the solution

declare
c_ip varchar2(15);
sql_stmt varchar2(100);
begin
:c_ip := SYS_CONTEXT('USERENV','IP_ADDRESS'); execute immediate 'alter session set sql_trace=true' ; execute immediate 'alter session set tracefile_identifier='''||:c_ip||'''' ;
end ;
/

here is the demonstration

hr_at_XE> connect hr/hr_at_xe;
Connected.
hr_at_XE> show parameter tracefile_identifier;

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
tracefile_identifier                 string

hr_at_XE> show parameter sql_tracefile;
hr_at_XE> show parameter sql_trace;

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
sql_trace                            boolean     FALSE

hr_at_XE> select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')

127.0.0.1

hr_at_XE> declare
  2 c_ip varchar2(15);
  3 sql_stmt varchar2(100);
  4 begin
  5 :c_ip := SYS_CONTEXT('USERENV','IP_ADDRESS');   6 execute immediate 'alter session set sql_trace=true' ;   7 execute immediate 'alter session set tracefile_identifier='''||:c_ip||'''' ;
  8 end ;
  9 /

PL/SQL procedure successfully completed.

hr_at_XE> show parameter tracefile_identifier;

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
tracefile_identifier                 string      127.0.0.1

C:\oraclexe\app\oracle\admin\XE\udump>dir Volume in drive C has no label.
Volume Serial Number is 4FD0-D7EB

Directory of C:\oraclexe\app\oracle\admin\XE\udump

21.03.2007  12:53    <DIR>          .
21.03.2007  12:53    <DIR>          ..
14.03.2007  13:02             1.200 10132.txt
07.03.2007  16:55               727 xe_ora_1216.trc
21.03.2007  12:54             2.177 xe_ora_3744_127.0.0.1.trc

..
..

On 3/21/07, Kumar, Arvind IN GGN SISL <arvind.kumar2_at_siemens.com> wrote:
>
>
>
> Hi All,
>
>
>
> Env : 10g 10.1.0.2.0, win2003
>
>
>
> Setting the sql_trace=true (at instance level) generates the trace
> filename in *instancename*_ora_*spid*.trc format, but I want the it to be
> in *client_identifier*_ora_*spid*.trc (i.e. 192.168.100.22_ora_3124.trc).
> It's needed to trace the session which are being generated from application
> server connection pool.
>
>
>
> Thanks
>
> Arvind Kumar
>
>
>

-- 
A. Coskan GUNDOGAR

Oracle DBA

http://coskan.wordpress.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 21 2007 - 05:56:49 CDT