TO enable the TRACE session level .................

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.pid, p.spid, s.sid, s.machine,s.serial#,lower(s.username) ora_user, p.username unix_user,Status,
s.osuser,
       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 s.sid=1764
order by s.logon_time,s.status;


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

SQL> oradebug setospid 26966

3. 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
c:\oracle9i\admin\ORCL92\udump\mooracle_ora_2280.trc


How to Trace(Reference)
----------------

Note:75713.1   Important Customer information about using Numeric Events
Note:21235.1   EVENT: 10262 "Do not check for memory leaks"
Note:21154.1   EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:160178.1  How to set EVENTS in the SPFILE
Note:1058210.6 HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG
Note:29062.1   SUPTOOL:  ORAMBX (VMS) - Quick Reference