Thursday, September 29, 2011

Finding the Trace Files


for setting up the trace environment in oracle, please read this post
http://www.bash-dba.com/2011/09/setting-up-environment-for-sql-tracing.html

Now coming back to our topic!

Often, it’s hard to find the exact trace file you’re looking for as there could be several files with more or less same kind of naming conventions. Therefore, best way to find out your trace file is to associate it with some kind of unique identifier.



Below alter session SQL command will setup the tracing identifier for you. Pl. issue this command before starting your tracing activitiy as discussed in http://www.bash-dba.com/2011/09/tracing-specific-sql-statement.html


SQL> alter session set tracefile_identifier='bilal_trace1';  

You can confirm the value of the trace identifier with the following command:


SQL> sho parameter tracefile_identifier

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tracefile_identifier                 string      bilal_trace1


SQL> host;

get to the 'ADRCI' utility from the host prompt

[oracle@test-lab trace]$ adrci


ADRCI: Release 11.2.0.2.0 - Production on Thu Sep 29 09:30:52 2011


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


ADR base = "/u01/app/oracle"

After getting in the adrci, issue the below statement to locate the trace file you just created


adrci>show tracefile -t

---

29-SEP-11 08:27:35  diag/rdbms/test_db/test_db/trace/test_db_ora_2575_bilal_trace1.trc


To find out the path to your current session’s trace file, issue the following command

SQL>  select value from v$diag_info 
      where name = 'Default Trace File'; 



VALUE
-----------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/test_db/test_db/trace/test_db_ora_3184.trc


To find all trace files for the current instance, issue the following query:

SQL> select value from v$diag_info where name = 'Diag Trace' 






No comments:

Post a Comment