TRACEFILE_IDENTIFIER – Another useful parameter
TRACEFILE_IDENTIFIER – Another useful parameter
Many 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’
/
1 Comment »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Fantastic site. Lots of useful information here. I am sending it to a few pals ans also sharing in delicious.
And obviously, thank you for your sweat!