Friday, December 24, 2010

Finding Your Trace File

This is an example using the tracefile_identifier parameter, which can make it easier to find your tracefiles. First show the value of this parameter, which will normally be null:

SQL> col tracefile_identifier format a30
SQL> select nvl(value, 'NULL') tracefile_identifier
2 from v$parameter where name = 'tracefile_identifier'
3 /

TRACEFILE_IDENTIFIER
------------------------------
NULL

SQL>

   
Now change this parameter:

SQL> alter session set tracefile_identifier = 'ANDREW'
2 /

Session altered.

SQL>

 
Start tracing your session and run some SQL:

SQL> alter session set sql_trace = true
2 /

Session altered.

SQL> col systimestamp format a40
SQL> select systimestamp from dual
2 /

SYSTIMESTAMP
----------------------------------------
22-DEC-10 04.11.27.125000 AM +00:00

SQL> alter session set sql_trace = false
2 /

Session altered.

SQL>

 

Now, when you look for the tracefile, the tracefile_identifier will form part of the name. Note that the SYSTIMESTAMP shown above is the time on the underlying operating system, which is not necessarily the same as the SYSDATE in the database. The SYSTIMESTAMP matches the update time of the tracefile as shown below:

C:\app\Andrew\diag\rdbms\adhoc\adhoc\trace>dir *ANDREW.trc
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\app\Andrew\diag\rdbms\adhoc\adhoc\trace

22/12/2010 04:11 2,041 adhoc_ora_2988_ANDREW.trc
            1 File(s)         2,041 bytes
            0 Dir(s) 13,303,558,144 bytes free

C:\app\Andrew\diag\rdbms\adhoc\adhoc\trace>

No comments: