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