Wednesday, February 2, 2011

Oracle 11g: Easy way getting current session's trace file name

As you know, sometimes we need to trace session, in order to troubleshoot application performance and etc. Tracing session generates server side file with name as: InstanceName_ora_SessionProcessID.trc.

To get these values and location of trace file, especially Process_ID, we had to join v$process with v$session.

However, in Oracle 11g, now it is possible to get current session's trace file name without writing custom SQL script:

SQL> desc v$diag_info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 INST_ID                                            NUMBER
 NAME                                               VARCHAR2(64)
 VALUE                                              VARCHAR2(512)


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

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

c:\app\oracle\diag\rdbms\oradbwin\oradbwin\trace\oradbwin_ora_1820.trc


So, it means if you trace this session, check above location for trace file.

No comments: