How to enable trace in Oracle

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

to Start  : sql_trace = TRUE

to Stop  : sql_trace = FALSE

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

2. Enable trace at session level

to Start  : ALTER SESSION SET sql_trace = TRUE;

to stop   : ALTER SESSION SET sql_trace = FALSE;

– or –

to Start  : EXECUTE dbms_session.set_sql_trace (TRUE);
to Stop   : EXECUTE dbms_session.set_sql_trace (FALSE);

– or –

to Start  : EXECUTE dbms_support.start_trace;
to Stop   : EXECUTE dbms_support.stop_trace;

3. Enable trace in another session

SELECT * FROM v$session WHERE upper(osuser) like  ‘&1’;

to start : EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop : EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

– or –

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

4. Enable trace Using ORADEBUG

SQL>  select s.username, p.spid os_process_id, p.pid oracle_process_id
  from v$session s, v$process p
where s.paddr = p.addr
   and s.username = upper(‘&user_name’);  2    3    4
Enter value for user_name: repadmin
old   4:    and s.username = upper(‘&user_name’)
new   4:    and s.username = upper(‘repadmin’)

USERNAME                       OS_PROCESS_I ORACLE_PROCESS_ID
—————————— ———— —————–
REPADMIN                       850297                      19
REPADMIN                       955904                      32
REPADMIN                       911971                      20
REPADMIN                       845029                      18
REPADMIN                       533889                      30

SQL> oradebug setospid 533889;
Oracle pid: 30, Unix process pid: 533889, image: oracle@bmau29.uk.pri.o2.com (TNS V1-V3)

SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.