Author: Jaffar

Oracle Trace File:

An Oracle Trace file is written when one of the Oracle background processes encounter an exception. An ORA-00600 error encountered when Oracle detects an unexpected condition also produces a trace. Along with ORA-00600, a trace file is written in the user_dump_dest or background_dump_dest directory. The name for the trace file written is recorded in the alert.log .

In this article I will discuss finding trace files in Oracle with special emphasis on finding trace file in Oracle 11g.

Changing the name of Oracle Trace File:

Below command can be used to change the name of the trace file to make it easier to find. A trace file will then have this identifier in its filename.

alter session set tracefile_identifier = 'some_id';

Specifying maximum size of Oracle Trace File:

The maximum size for trace files can be specified with max_dump_file_size

Using Data Dictionary Views to find Trace Files in Oracle 11g:

In Oracle 11g trace files are stored in the trace directory under Automatic Diagnostic Repository (ADR) home. Oracle data dictionary views can be used to get the location of individual trace files.

Finding Oracle Trace File for Current Session:

Below query can be used to find the trace file for your current session. This query will return the full path to the trace file.

SELECT value
FROM v$diag_info
WHERE name = ‘Default Trace File'; Continued...

Finding Oracle Trace File for Current Instance:

You can run below query to find all trace files for the current instance. It will return the full path to the ADR trace directory.

SELECT value
FROM v$diag_info
WHERE name = ‘Diag Trace';

Finding Oracle Trace File for Current Database Process:

Below query can determine the trace file for each Oracle Database process. It will return the full path to the trace file with its respective process id.

SELECT pid, program, tracefile
FROM v$process;

Using Data oradebug to find Trace Files in Oracle 11g:

You can use oradebug to find the name of a trace file.

SQL> oradebug setmypid

SQL> oradebug tracefile_name

Script to find Oracle Trace File:

You can run below script to return the path to the trace file that the current session writes. It returns the path whether or not tracing is enabled.

select
u_dump.value || '/' ||
db_name.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 db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where
u_dump.name = 'user_dump_dest' and
db_name.name = 'db_name' and
v$session.audsid=sys_context('userenv','sessionid');

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for determining Optimal Online Redo Log File Size!!
   The power of Oracle10g Remote Stored Functions!!
   What if your database lock gets blocked??
   Oracle-SQL Guide: Look out for Fragmented Indexes