Re: Destination for the SQL trace file?

From: Edorta Simon <esd20_at_tid.es>
Date: Thu, 30 Sep 1999 17:10:24 +0200
Message-ID: <37F37D60.A0AC44AF@tid.es>

By running this script you'll enable trace and you'll have the full name of the file in the variable "trace_file_name". Beware of the ordinal part of the name (l_pid variable), which is in hexadecimal format.

Erika Grondzakova wrote:

> Hello,
>
> How could I find out (not having the DBA privilege) where is the
> destination for the trace file generated be the SQL trace facility?
>
> Thanks in advance,
>
> Erika Grondzakova

declare

    cursor udd_csr is

        select value 
          from v$parameter
         where name = 'user_dump_dest';
    cursor my_pid_csr is
        select spid
          from v$process
         where addr = (select paddr
                         from v$session
                        where audsid=userenv('sessionid'));
    l_user_dump_dest    varchar2(256);
    l_pid               varchar2(20);

begin

• set trace on dbms_session.set_sql_trace(true);
• get user_dump_dest open udd_csr; fetch udd_csr into l_user_dump_dest; close udd_csr;
• get process id for the shadow open my_pid_csr; fetch my_pid_csr into l_pid; close my_pid_csr;
• return the name of the trace file dbms_output.put_line(l_user_dump_dest||'\ora'||l_pid||'.trc'); :trace_file_name := l_user_dump_dest||'\ora'||l_pid||'.trc'; end; /

Received on Thu Sep 30 1999 - 10:10:24 CDT