Querying the current tracefile name, using SQL – with tracefile_identifier

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.

5 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…

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>