Querying the current tracefile name, using SQL – with tracefile_identifier

Tanel Poder

2008/05/30

_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: