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.

Advertisements

About mgogala

I am a consultant with an extensive Oracle DBA experience. I have worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Finding the trace files

  1. John says:

    It was in v$process since 11g

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s