jump to navigation

How to identify trace file in USER_DUMP_DIRECTORY? September 13, 2009

Posted by sendtoshailesh in Tuning.
Tags: , ,

ORA-600 or ORA-7445 kind of errors are always have lot of things hidden which DBA needs to determine by taking session trace of various database components. There are various way of taking these dumps like enabling session trace, using alter session command, end to end tracing, or by using oradebug utility.

Once we give command to generate trace oracle saves the trace in file in directory configured in USER_DUMP_DEST parameter. Once the tracing ends, we need to find the file according to particular naming convention. Basically, file name have instance name and OS PID through which trace file is generated. The following script returns the path to the trace file that the current session writes.
It returns the path whether or not tracing is enabled.

SQL> select u_dump.value || ‘/’ || instance.value || ‘_ora_’ || v$process.spid
|| nvl2(v$process.traceid, ‘_’ || v$process.traceid, null ) || ‘.trc'”Trace File”
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = ‘user_dump_dest’
and instance.name = ‘instance_name’
and V$SESSION.audsid=sys_context(‘userenv’,’sessionid’);

Trace File

16 rows selected.

These files can be further processed by TRCSESS, TKPROF tools to get reports in expected format. Because actual trace file have huge dump of data, which can not be understood without having the expertize of examining the trace.



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: