How to Enable trace on a session in Oracle


1.  Enable trace at instance level:

ALTER SYSTEM SET trace_enabled = TRUE;

ALTER SYSTEM SET trace_enabled = FALSE;

2.  Enable trace at session level:

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

3.  Find out SID and SERIAL# from v$session of that particular user

select sid, serial# from v$session where username = ‘USER’;

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


Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."