Querying the current tracefile name, using SQL – with tracefile_identifier

Update: In Oracle 11g upwards you can use V$DIAG_INFO for getting your own session’s trace file name too (diag.sql script). But on earlier versions or if you want to see the current tracefile name of another, you still need to use the V$PROCESS approach below. Note that V$PROCESS in 11g (or 11.2?) has a column TRACEFILE that shows the entire path+name of a process tracefile (however there seems to be a bug in 12.1.0.2 where the field is always null).

Here’s a code snippet for identifying current tracefile name using SQL. Yep I know there are many such examples online, but I haven’t found any so far which also account for TRACEFILE_IDENTIFIER variable.

Luckily the value of this variable is accessible from V$PROCESS, so we can write a query which constructs us the full tracefile name, including TRACEFILE_IDENTIFIER part, if any:

SQL> alter session set tracefile_identifier = blah;

Session altered.

SQL> select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
  2         (select spid||case when traceid is not null then '_'||traceid else null end
  3               from v$process where addr = (select paddr from v$session
  4                                           where sid = (select sid from v$mystat
  5                                                      where rownum = 1
  6                                                 )
  7                                      )
  8         ) || '.trc' tracefile
  9  from v$parameter where name = 'user_dump_dest'
 10  /

TRACEFILE
---------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc

1 row selected.

Let’s see if this actually worked:

LIN11G1$ head /u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc
Trace file /u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      linux01
Release:        2.6.18-53.el5
Version:        #1 SMP Sat Nov 10 18:24:52 EST 2007
Machine:        i686
LIN11G1$

Yep! :)

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

10 Responses to Querying the current tracefile name, using SQL – with tracefile_identifier

  1. Ah … excellent …

  2. Quick note for 11g users; V$DIAG_INFO view gives the trace file name with TRACEFILE_IDENTIFIER that is set :)

  3. Coool…

    Good one !

  4. tanelp says:

    Thanks Tonguç, I didn’t know that…

  5. Interesting, but is there a case where v$process.tracefile is wrong?

    SQL> @q_my_tracefile

    INST_ID SID SERIAL# PID
    ———- ———- ———- ———-
    TRACEFILE
    ——————————————————————————–
    1 133 2351 26
    c:\ora\diag\rdbms\rsiz\rsiz\trace\rsiz_ora_24424.trc

    SQL> l
    1 select s.inst_id,s.sid,s.serial#,p.pid, p.tracefile
    2 from gv$process p,
    3 (select inst_id, sid, serial#, paddr
    4 from gv$session
    5 where sid in (select sid from v$mystat where rownum alter session set tracefile_identifier = mwf;

    Session altered.

    SQL> @q_my_tracefile

    INST_ID SID SERIAL# PID
    ———- ———- ———- ———-
    TRACEFILE
    ——————————————————————————–
    1 133 2351 26
    c:\ora\diag\rdbms\rsiz\rsiz\trace\rsiz_ora_24424_MWF.trc

    SQL>

    • Tanel Poder says:

      Yeah I wrote that article quite some time ago… before 11g I think … should probably update it (some year :-) … but nowadays I query v$diag_info (@diag.sql) script

  6. SQL> @q_my_tracefile

    INST_ID SID SERIAL# PID
    ———- ———- ———- ———-
    TRACEFILE
    ——————————————————————————–
    1 133 2351 26
    c:\ora\diag\rdbms\rsiz\rsiz\trace\rsiz_ora_24424.trc

    SQL> l
    1 select s.inst_id,s.sid,s.serial#,p.pid, p.tracefile
    2 from gv$process p,
    3 (select inst_id, sid, serial#, paddr
    4 from gv$session
    5 where sid in (select sid from v$mystat where rownum alter session set tracefile_identifier = mwf;

    Session altered.

    SQL> @q_my_tracefile

    INST_ID SID SERIAL# PID
    ———- ———- ———- ———-
    TRACEFILE
    ——————————————————————————–
    1 133 2351 26
    c:\ora\diag\rdbms\rsiz\rsiz\trace\rsiz_ora_24424_MWF.trc

    SQL>

    Not sure why part of two key lines overwrote each other previously.

  7. argh, cannot see why editor is wiping out lines when I hit post:

    select s.inst_id,s.sid,s.serial#,p.pid, p.tracefile
    from gv$process p,
    (select inst_id, sid, serial#, paddr
    from gv$session
    where sid in (select sid from v$mystat where rownum < 2)
    ) s
    where p.addr = s.paddr

  8. How about the column next to traceid: TRACEFILE VARCHAR2(513) Trace file name of the process?


    select tracefile
    from v$process p
    , v$session s
    where p.addr = s.paddr
    and s.sid = (select m.sid
    from v$mystat m
    where rownum = 1
    )
    ;

Leave a Reply

Your email address will not be published. Required fields are marked *