The query would then look like this:
1 SELECT S.USERNAME,S.SID,P.SPID
2 FROM V$SESSION S,V$PROCESS P
3 WHERE S.PADDR=P.ADDR AND
4* S.USERNAME='SCOTT'
SQL> /
USERNAME SID SPID
------------------------------ ---------- ------------
SCOTT 36 12518


Attaching the debugger to the given session can now be done by the following command:

SQL> oradebug setospid 12518
Oracle pid: 19, Unix process pid: 12518, image: oracle10g@medo.noip.com

Suspending and resuming sessions
This example is given only for completeness, In reality, need for suspending user sessions arises
very infrequently, and even then, we should look for an alternative solution, if possible.
Suspending and resuming a session is an internal operation and, as such, should be used only when
truly necessary or requested by oracle support.
We can suspend the session that we have attached our debugger to, by using the following
command:

SQL> oradebug suspend
Statement processed.

The process that debugger is attached to will be suspended. It is important not to try that with the
sessions that are connected through the dispatcher (MTS) because, the session will find
another free shared server process to execute its SQL command. This is well suited for dedicated
server sessions only. The dedicated server session that I attached my process to
will appear to be hanging and will not react to SQL. Reaction will come only when I resume
the session with the following command:

SQL> oradebug resume
Statement processed.

только на это надо привелегии sysdba