How to find a trace file for a session

If you’ve run a SQL trace on a user session and are hunting around trying to find which trace file contains the output you can run two queries within SQL to quickly identify which file you are looking for:

Example:

Find a tracefile for the user ABCTAX (can only be used when the session is running)

SQL> select username, sid from v$session where upper(username) like ‘%ABC%’;

USERNAME SID
—————————— ———-
ABCTAX 390

 

SQL> select tracefile from v$process join v$session on (addr=paddr) where sid = 390;

TRACEFILE
——————————————————————————–
/tpp/oracle/diag/rdbms/rbwform/RBWFORM/trace/RBWFORM_ora_9830876.trc