How to Enable User-Level SQL Tracing in Oracle (Both Current and Remote Sessions)

In this article, I’m going to explain user-level SQL tracing in Oracle — a very useful technique when you need to diagnose performance issues or understand what SQL a session is executing.
We’ll look at two scenarios:

Tracing your own (current) session
Tracing another user’s session from outside

Most of the time, DBAs trace other sessions, so both approaches are covered below.

1. Tracing the Current Session

You can enable SQL trace in your own session with a simple command.

Start tracing


ALTER SESSION SET sql_trace = true;

Note: The user must have ALTER SESSION privilege.

Optional: Add an identifier to the trace file

This makes it easier to find the correct file later:


ALTER SESSION SET tracefile_identifier = himanshu;

Find the location of your trace file

Run:


SELECT value 
FROM gv$diag_info 
WHERE name = 'Default Trace File';

Example output:


/u01/app/oracle/diag/rdbms/test12c/TEST12c1/trace/
TEST12c1_ora_13311_himanshu.trc

Stop tracing


ALTER SESSION SET sql_trace = false;

2. Tracing Another User’s Session

Sometimes you need to trace a session that is already running. For that, you must identify the session details first.

Find the SID and SERIAL#


SELECT inst_id, sid, serial#
FROM gv$session 
WHERE username = '{USERNAME}';

Start tracing for that session


EXECUTE dbms_system.set_sql_trace_in_session({SID}, {SERIAL#}, TRUE);

Note:
In a RAC environment, run this command on the same node where the session is connected.

Locate the trace file


SELECT p.username, p.program, p.tracefile 
FROM gv$process p 
JOIN gv$session s
   ON p.addr = s.paddr 
  AND p.inst_id = s.inst_id
WHERE s.sid = {SID}
AND s.inst_id = {INST_ID};

Stop tracing


EXECUTE dbms_system.set_sql_trace_in_session({SID}, {SERIAL#}, FALSE);

Again, on RAC systems, make sure to execute this on the correct instance.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

How to Enable User-Level SQL Tracing in Oracle (Both Current and Remote Sessions)