Trace Files -- 4 : Identifying a Trace File

This post updated on 21-Oct-15 to show retrieval of the tracefile name from v$process
The server processid 2992 was on 18-Oct.  The 21-Oct server processid was 3079.

11g has a V$SQL_DIAG that one can use to identify a session's own trace file.

SQL> select name, value                             
  2  from v$diag_info
  3  where name = 'Default Trace File'
  4  /

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Default Trace File
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2992.trc

SQL>

Thus, my current session's trace file name is displayed. If I (or the DBA) enable tracing for my session, this would be where the trace would be captured.

The DBA can identify the tracefile for a session.   In earlier versions, the instance parameters user_dump_dest and  background_dump_dest would be set to define the location of trace files. 11g relies on diagnostic_dest and automatically derives the user / background dump dests.

SQL> show parameter diag

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest        string  /u01/app/oracle
SQL> show parameter user

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
license_max_users       integer  0
parallel_adaptive_multi_user      boolean  TRUE
redo_transport_user       string
user_dump_dest        string  /u01/app/oracle/diag/rdbms/orc
       l/orcl/trace
SQL>

The actual trace file name can then be identified for a given session where we know the USERNAME / SID / SERIAL# values in v$session as :

SQL> l
  1  select p.value || '/' || instance_name || '_ora_' || p.spid || '.trc'
  2  from v$parameter p, v$process p, v$session s , v$instance
  3  where
  4  p.name = 'user_dump_dest'
  5  and
  6  s.username = 'HEMANT'
  7  and s.sid = 145
  8  and s.serial#=11
  9* and p.addr=s.paddr
SQL> /

P.VALUE||'/'||INSTANCE_NAME||'_ORA_'||P.SPID||'.TRC'
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2992.trc

SQL> 

UPDATE 21-Oct-15 :  Actually, 11g does present the tracefile name in V$PROCESS.  So, the query can be simplified as :

SQL> select s.sid, s.serial#, p.spid, p.tracefile
  2  from v$session s, v$process p
  3  where s.paddr=p.addr
  4  and s.username = 'HEMANT'
  5  order by 1;

       SID    SERIAL# SPID
---------- ---------- ------------------------
TRACEFILE
--------------------------------------------------------------------------------
       146     5 3079
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3079.trc


SQL> 

However, a user session can change the name of it's trace file to append a desired string with :

SQL> alter session set tracefile_identifier='Hemant';

Session altered.

SQL> select value                 
  2  from v$diag_info
  3  where name = 'Default Trace File'
  4  /

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2992_Hemant.trc

SQL> 

The tracefile_identifer can be changed as many times as desired as in a session while it is connected.
This actually allows the session to create new (distinct) trace files as and when desired.  One set of SQL operations in the session may be done with tracefile_identifier='Hemant' resulting in the file orcl_ora_2992_Hemant.trc.  Thereafter, without disconnecting the session, it may define a different tracefile_identifier='Chitale' and execute another set of SQL operations.  This second set of SQL operations would go to a trace file orcl_ora_2992_Chitale.trc   Notice that the SPID (2992) doesn't change but the actual trace file name does change.

 However, if  a session sets or changes it's own tracefile_identifier the DBA query shown earlier cannot detect this.

UPDATE 21-Oct-15: The DBA can query v$process to get the new tracefilename :

SQL> l
  1  select s.sid, s.serial#, p.spid, p.tracefile
  2  from v$session s, v$process p
  3  where s.paddr=p.addr
  4  and s.username = 'HEMANT'
  5* order by 1
SQL> /

       SID    SERIAL# SPID
---------- ---------- ------------------------
TRACEFILE
--------------------------------------------------------------------------------
       146     5 3079
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3079_Hemant.trc


SQL> 

.
.
.