Finding the trace files

In Oracle versions before 12c, it was necessary to resort to complex magic to find out the location of the process trace file. One of the most famous recipes is probably the one invented by René Nyffenegger and available here:

http://www.adp-gmbh.ch/ora/misc/find_trace_file.html

Another trick included utilizing ORADEBUG command “tracefile_name” which would return trace file name.

However, in Oracle 12c the things have changed. Trace file is now available as a column in the V$PROCESS view:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> desc v$process
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 ADDR RAW(8)
 PID NUMBER
 SOSID VARCHAR2(24)
 SPID VARCHAR2(24)
 STID VARCHAR2(24)
 EXECUTION_TYPE VARCHAR2(10)
 PNAME VARCHAR2(5)
 USERNAME VARCHAR2(15)
 SERIAL# NUMBER
 TERMINAL VARCHAR2(16)
 PROGRAM VARCHAR2(64)
 TRACEID VARCHAR2(255)
 TRACEFILE VARCHAR2(513)
 BACKGROUND VARCHAR2(1)
 LATCHWAIT VARCHAR2(16)
 LATCHSPIN VARCHAR2(16)
 PGA_USED_MEM NUMBER
 PGA_ALLOC_MEM NUMBER
 PGA_FREEABLE_MEM NUMBER
 PGA_MAX_MEM NUMBER
 CON_ID NUMBER

It is also documented in the documentation. Getting trace file is now a breeze:

SQL> select username,sid from v$session where username='SYSTEM';
USERNAME      SID
----------------------------- ----------
SYSTEM        362
Elapsed: 00:00:00.13
SQL> select p.tracefile from v$process p join v$session s  
2  on (p.addr=s.paddr) where s.sid=362;
TRACEFILE
--------------------------------------------------------------------------------
C:\APP\ORACLE\diag\rdbms\oraprod\oraprod\trace\oraprod_ora_11104.trc
Elapsed: 00:00:00.15

That makes the life of a DBA significantly easier.