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

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.

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 = 1179

       SID    SERIAL#
---------- ----------
TRACE_FILE
--------------------------------------------------------------------------------
      1179      23589
/u01/app/oracle/diag/rdbms/devdb2/DEVDB21/trace/DEVDB21_ora_8156.trc
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google