Identifying trace file.         31/mar/2006

Tracing SQL statement is vital technique to diagnosing poorly tuned SQL statement.

A common technique is to user SQL_TRACE to dump Diagnostic informaiton

SQL> Alter session set SQL_TRACE = TRUE;

The above statement dumps a trace file to the UDUMP directory.

The name of the trace file depends on the operating system process ID that running the session executing the SQL statement.

To make it easier for you to identify the trace file, you can use the following statement:

SQL>Alter session set tracefile_identifier = 'ammar';

then enable SQL tracing

SQL>Alter session set tracefile_identifier ='ammar';

then execute any statemet

SQL>SELECT * from EMP where empno =7499;

The following shows the name of the dump file generated by the above sequence of command.

$pwd
/home/oracle10g/product/10.1.0/Db_1/admin/orclstc/udump
$ ls -l *ammar*
-rw-r----- 1 oracle10g oinstall 10072 Mar 31 10:41 orclstc_ora_9530_ammar.trc
 

It is now clear that the word ammar is appended to the end of the dump file name. It is know easier to identify tracefile  :)