How to get a tracking file name

Original link:

http://www.eygle.com/faq/how.to.get.tracefile.name.htm

When we use SQL_Trace / 10046 and other incidents, the tracking file is generated. The trace file name consists of the following parts:

_ORA_ .TRC

The following script users get the tracking file name:

For UNIX:

$ SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.4.0 - Production On Fri Oct 8 12:08:09 2004

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS

JServer Release 9.2.0.4.0 - Production

SQL> Set echo on

SQL> @gettrcnameunix

SQL> SELECT D.VALUE

2 || '/'

3 || Lower (RTRIM (I.instance, Chr (0)))

4 || '_ora_'

5 || P.SPID

6 || '.trc' trace_file_name

7 from (SELECT P.SPID)

8 from V $ MyStat M, V $ Session S, V $ Process P

9 WHERE M.STATISTIC # = 1 and s.SID = m.sid and p.addr = spaddr) p,

10 (SELECT T.INSTANCE

11 from V $ Thread T, V $ Parameter V

12 where v.name = 'thread'

13 and (v.value = 0 or t.thread # = to_number (v.value))) i,

Select Value

15 from V $ Parameter

16 where name = 'user_dump_dest') d

17 /

TRACE_FILE_NAME

-------------------------------------------------- ------------------------------

/opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc

For NT:

SQL> SELECT D.VALUE

2 || '/'

3 || Lower (RTRIM (I.instance, Chr (0)))

4 || '_ora_'

5 || P.SPID

6 || '.trc' trace_file_name

7 from (SELECT P.SPID)

8 from V $ MyStat M, V $ Session S, V $ Process P

9 where m.statistic # = 1 and s.SID = m.sid and p.addr = spaddr) P, 10 (Select T.Instance

11 from V $ Thread T, V $ Parameter V

12 where v.name = 'thread'

13 and (v.value = 0 or t.thread # = to_number (v.value))) i,

Select Value

15 from V $ Parameter

16 where name = 'user_dump_dest') d

17 /

TRACE_FILE_NAME

-------------------------------------------------- ------------------------------

E: /oracle/admin/eygle/udump/EYGLE_ORA_3084.TRC

You can download the above scripts here: ForumiX Fornt

Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.

Original source:

http://www.eygle.com/faq/how.to.get.tracefile.name.htm