TRACEFILE_IDENTIFIER - Another useful parameter

Many a times you need to enable tracing at a session level. And when you do that, you have to make a little bit of effort in digging out your session's trace file. Why? because the trace file is created in the user_dump_dest directory and there would be so many other trace files, and all of them would have similar naming convention "SID_ora_nnnn.trc". However with the help of the parameter "TRACEFILE_IDENTIFIER", you can easily distinguish your trace file. Very useful specifically when you want to use trace analyzer etc.

Here is how?

1. Set the tracefile identifier as you want, this will be appended to trace file name.
alter session set tracefile_identifier='sipras_sql_trace';

2. Enable your session level tracing
alter session set sql_trace=true;
alter session set events '10046 trace name context forever, level 12' ;

3. Execute your queries/statements

4. Turn-off tracing
alter session set events '10046 trace name context off';
alter session set sql_trace=false;

5. Check the user_dump_dest folder and you will find a file name "SID_ora_nnnn_sipras_sql_trace.trc

See now it's so easy to identify. Having said that you can still find out the trace file name without using TRACEFILE_IDENTIFIER parameter using the following SQL but when Oracle has provided an easier method, why not use it?

-- sql to find out trace file name for your session.

select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/