Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 6 December 2012


This parameter is used specifies custom identifier with the oracle trace file format to identify a trace file simply from its name and without having to open it or view its contents. Generally when you enable tracing at a session level, you have to make a little bit of effort in digging out your session's trace file because in the user_dump_dest directory there would be so many other trace files, and all of them would have similar naming convention "SID_ora_nnnn.trc". Through the use of this parameter, you can easily distinguish your trace file. It is useful specifically when you want to use trace analyzer etc.
Enable Tracing:
Alter session set tracefile_identifier='sadhan_sql_trace';
Alter session set sql_trace=true;
Alter session set events ‘10046 trace name context forever, level 12’;
Now try to execute your queries and statements and check the user_dump_dest folder there you will find the file name "SID_ora_nnnn_sadhan_sql_trace.trc”.
Note: This parameter can only be used to change the name of the foreground process' trace file; the background processes continue to have their trace files named in the regular format. 
Disable Tracing:
Alter session set events ‘10046 trace name context off’;
Alter session set sql_trace=false;
You can directly find out the trace file name without using TRACEFILE_IDENTIFIER parameter though the SQL query.
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 = 'user_dump_dest';


Post a Comment