Is session Tracing on for Oracle session ?

sql_trace—Shows- (TRUE/FALSE) if SQL tracing has been enabled in the session
sql_trace_waits- If session tracing is enabled, you can have the trace write wait information to the trace file;
sql_trace_binds- If the session uses bind variables, This column shows TRUE/FALSE.



SQL> select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session where username = 'SCOTT' ;

SID SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
42 8482 DISABLED FALSE FALSE


From this query we notice that tracing is not enabled in the session with SID 42 and Serial# 8482

Now, enable tracing of wait events, but not of bind variables.

Oracle package dbms_monitor to enable tracing.

begin
dbms_monitor.session_trace_enable (
session_id => 42,
serial_num => 8482,
waits => true,
binds => false
);
end;
/


select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds from v$session
where username = 'SCOTT' ;


SID SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
42 8482 ENABLED TRUE FALSE


View V$SESSION is populated only if the procedure session_trace_enable
in the package dbms_monitor is used to enable tracing,
not by alter session set sql_trace = true or setting the event 10046