How to Find Out Oracle Trace File Name for TKPROF or backup controlfile to trace?

advertisements

_____________________________________________________________________________________________________________________

Suppose if you traced your own session and you wanted to find out the trace file name from the database itself. You can use the following query. One thing you need to change the slash which is marked in red below depends on the operating system. For Windows use \ and for unix use /. The same query can be used for finding out the trace file name when you trace the control file also.

1. Trace current session

SQL> alter session set sql_trace=true;

Session altered.

2. Backup the controlfile to trace

SQL> alter database backup controlfile to trace;

Database altered.

SQL> SELECT sess.sid,

sess.serial#,

param.value || '/' || upper(SYS_CONTEXT('userenv','instance_name')) || '_ora_' || process.spid || '.trc' AS trace_file

FROM v$session sess,

v$process process,

v$parameter param

WHERE param.name = 'user_dump_dest'

AND sess.paddr = process.addr

AND sess.sid = SYS_CONTEXT('USERENV', 'SID');

SID SERIAL#

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

TRACE_FILE

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

1179 23589

/u01/app/oracle/diag/rdbms/devdb2/DEVDB21/trace/DEVDB21_ora_8156.trc

Suppose if you traced a session from another user and you are aware of the SID for the session, then you can use the following query. Please find the change marked in red color below.