How to enable trace for a database session

This article lists the commands required to trace SQL statements executed by a user or an application.

Tracing a SQL session

Start session trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = sampletrace;

Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing other user's sessions

We can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions.

In order to enable trace for other session, first thing we need is to identify their session sid and serial#.

Enable tracing for your selected process:

ALTER SYSTEM SET timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, true);

Now ask the user to reproduce the problem

Disable tracing for your selected process:

execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, false);

Location of Trace File:

We can identify the trace file for a specific session using the V$SESSION and V$PROCESS views.

SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = <sid>;