Just a quick example showing how to identify trace files for current session and other sessions.
Basically, there are 2 methods to identify current trace file for current session and only 1 way for other session (or what I know how to)
set lines 130
col sql_trace for a15
col sql_trace_waits for a15
col sql_trace_binds for a15
col traceid for a15
col tracefile for a100
col value for a100
set echo on
SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE audsid = USERENV ('SESSIONID');
select value from v$diag_info where name='Default Trace File';
Identifying Trace File for Current Session:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @tracefile
SQL> show user
USER is "SCOTT"
SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
2 FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
3 WHERE audsid = USERENV ('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
--------------- --------------- --------------- ---------------
TRACEFILE
----------------------------------------------------------------------------------------------------
DISABLED FALSE FALSE
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc
SQL> select value from v$diag_info where name='Default Trace File'; VALUE
----------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc
SQL> alter session set tracefile_identifier='traceme';
Session altered.
SQL> @tracefile
SQL> show user
USER is "SCOTT"
SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
2 FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
3 WHERE audsid = USERENV ('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
--------------- --------------- --------------- ---------------
TRACEFILE
----------------------------------------------------------------------------------------------------
DISABLED FALSE FALSE traceme
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc
SQL> select value from v$diag_info where name='Default Trace File'; VALUE
----------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc
Identifying Trace File for Other Session:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set lines 130 col sql_trace for a15 col sql_trace_waits for a15 col sql_trace_binds for a15 col traceid for a15 col tracefile for a100 col value for a100SQL> SQL> SQL> SQL> SQL> SQL> SQL> show user USER is "SYSTEM" SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, p.traceid, p.tracefile FROM v$session s JOIN v$process p ON (p.addr = s.paddr) WHERE s.username='SCOTT'; 2 3 SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID --------------- --------------- --------------- --------------- TRACEFILE ---------------------------------------------------------------------------------------------------- DISABLED FALSE FALSE /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc SQL> / SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID --------------- --------------- --------------- --------------- TRACEFILE ---------------------------------------------------------------------------------------------------- DISABLED FALSE FALSE traceme /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc
