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! :)



Ah … excellent …
Comment by David Aldridge — May 31, 2008 @ 10:14 pm
Quick note for 11g users; V$DIAG_INFO view gives the trace file name with TRACEFILE_IDENTIFIER that is set :)
Comment by H.Tonguç Yılmaz — June 1, 2008 @ 1:13 am
Coool…
Good one !
Comment by Amardeep Sidhu — June 1, 2008 @ 1:48 am
Thanks Tonguç, I didn’t know that…
Comment by tanelp — June 1, 2008 @ 11:59 am
Nice one …
Comment by Asif Momen — June 1, 2008 @ 7:21 pm
[...] following part describes how to use [...]
Pingback by Identify the name of Trace file by using Tracefile_Identifier | Oracle Point, Oracle Life. — June 28, 2008 @ 7:21 am
[...] following part describes how to use [...]
Pingback by Identify Trace File Name by using Tracefile_Identifier | Oracle Point, Oracle Life. — June 28, 2008 @ 7:32 am
[...] following part describes how to use [...]
Pingback by Identifying Trace File Name by using Tracefile_Identifier | Oracle Point, Oracle Life. — July 4, 2008 @ 7:10 am