To enable the oracle user session

column spid format a5
column sid format 9999
column ora_user format a10
column unix_user format a10
column osuser format a10
column server for a12
column machine for a15
col MACHINE for a10
column whenon format a18 heading "WHEN USER LOGGED ON"
column whendo format a18 heading "WHEN LAST ACTIVITY"
set lin 150
select  p.spid, s.sid, s.event,s.module,s.machine,s.serial#,Status,
       to_char(s.logon_time,'mm/dd/yy hh24:mi:ss') whenon,
       to_char(sysdate - (s.last_call_et) / 86400,'mm/dd/yy hh24:mi:ss') whendo
from v$process p, v$session s
where s.paddr(+) = p.addr  and sid=617
order by s.logon_time,s.status;

2. Start the debug session with the SPID of the process that needs traced.




SQL> oradebug setospid 32230


. Select the appropriate trace level. There are four different options when specifying a tracing level:

• Level 1 – provides “base set” tracing information. Bind variables are displayed as variables (:b1).

• Level 4 – provides Level 1 data and the actual data values of bind variables.

• Level 8 – provides Level 1 data and information on wait events when the elapsed time is greater than the CPU time.

• Level 12 – combines levels 1, 4 and 8 tracing information.

A Level 12 trace contains base set, bind variable values and wait events.
The oradebug command below will enable the maximum tracing possible:

SQL> oradebug event 10046 trace name context forever, level 12




4. Turn tracing off.

SQL> oradebug event 10046 trace name context off

5. Obtain the trace file name. The oradebug facility provides an easy way to obtain the file name:

SQL> oradebug tracefile_name