in Oracle

Trace Files — 4 : Identifying a Trace File

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>

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.
.
.
.