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