Thinking Out Loud

October 6, 2012

Identifying Trace File 11.2

Filed under: 11g,oracle — mdinh @ 2:21 pm

Just a quick example showing how to identify trace files for current session and other sessions.

Basically, there are 2 methods to identify current trace file for current session and only 1 way for other session (or what I know how to)

set lines 130
col sql_trace for a15
col sql_trace_waits for a15
col sql_trace_binds for a15
col traceid for a15
col tracefile for a100
col value for a100
set echo on
SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE audsid = USERENV ('SESSIONID');
select value from v$diag_info where name='Default Trace File';

Identifying Trace File for Current Session:

 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options 
 SQL> @tracefile
 SQL> show user
 USER is "SCOTT"
 SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
 2 FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
 3 WHERE audsid = USERENV ('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
 --------------- --------------- --------------- ---------------
 TRACEFILE
 ----------------------------------------------------------------------------------------------------
 DISABLED FALSE FALSE
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc 

 SQL> select value from v$diag_info where name='Default Trace File'; VALUE
 ----------------------------------------------------------------------------------------------------
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc
 
 SQL> alter session set tracefile_identifier='traceme'; 
 Session altered. 
 SQL> @tracefile
 SQL> show user
 USER is "SCOTT"
 SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
 2 FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
 3 WHERE audsid = USERENV ('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
 --------------- --------------- --------------- ---------------
 TRACEFILE
 ----------------------------------------------------------------------------------------------------
 DISABLED FALSE FALSE traceme
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc 

 SQL> select value from v$diag_info where name='Default Trace File'; VALUE
 ----------------------------------------------------------------------------------------------------
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc

Identifying Trace File for Other Session: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set lines 130
col sql_trace for a15
col sql_trace_waits for a15
col sql_trace_binds for a15
col traceid for a15
col tracefile for a100
col value for a100SQL> SQL> SQL> SQL> SQL> SQL> 
SQL> show user
USER is "SYSTEM"
SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, p.traceid, p.tracefile
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE s.username='SCOTT'; 2 3
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
--------------- --------------- --------------- ---------------
TRACEFILE
----------------------------------------------------------------------------------------------------
DISABLED FALSE FALSE
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc

SQL> /
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
--------------- --------------- --------------- ---------------
TRACEFILE
----------------------------------------------------------------------------------------------------
DISABLED FALSE FALSE traceme
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 318 other followers

%d bloggers like this: