TRACEFILE_IDENTIFIER

TRACEFILE_IDENTIFIER

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 c.name = 'user_dump_dest';