SPID | SID | PID Explained

--------------------------------

SPID | SID | PID Explained

--------------------------------

To dump trace and errorstacks, either the Operating system process id or Oracle process id for a slave process must be determined.
Assuming the Oracle SID for the process is known then the following select can be
used to find the Operating system process id :

SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.SID = &SID;

SPID is the operating system identifier
SID is the Oracle session identifier
PID is the Oracle process identifier

and then do the following:

Lets assume that the slave process id to be dumped has an o/s pid of 9834
and an Oracle pid of 34

login to SQL*Plus:

connect / as sysdba
ALTER SESSION SET tracefile_identifier = 'STACK_10046';
oradebug setospid 9834
oradebug unlimit

oradebug event 10046 trace name context forever,level 12

oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name

To us the Oracle Pid instead, replace the :

oradebug setospid 9834

with

oradebug setorapid 34

Remember to change the PIDs to the actual values on your system!

The final line outputs the trace file name which will include the string 'STACK_10046' for easy identification.
To disable the tracing once tracing is finished:

oradebug event 10046 trace name context off

This will produce a trace file in the relevant trace destination matching the process traced.

Gathering Errorstacks on Your Current session

If you are trying to gather stacks on your current session, you can determine the PID values by running selects such as the following in that session:

SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.audsid = userenv('SESSIONID') ;

or

SELECT p.pid, p.SPID,s.SID
FROM v$process p,v$session s
WHERE s.paddr = p.addr
AND s.SID =
(SELECT DISTINCT SID
FROM V$MYSTAT);

Command Explanation

The following is a brief outline of the purpose of the commands:

• connect / as sysdba
  This provides the simplest way of gathering the required access to dump the information.
  
• ALTER SESSION SET tracefile_identifier = 'STACK_10046';
  Adds a search-able string to the trace file for easy identification later.
  
• oradebug setospid XXXX
  Connects the oradebug tool to a particular O/S (operating system) PID (Process ID)
  
• oradebug setorapid XXXX
  Connects the oradebug tool to a particular oracle PID (Process ID)
  
• oradebug unlimit
  Unrestricts the trace file size so as not to lose valuable diagnostics.
  
• oradebug event 10046 trace name context forever,level 12
  Collects 10046 trace showing waits and binds. This trace is ofter useful when collected in conjunction with errorstacks and providing context. This is optional.
  
• oradebug dump errorstack 3
  Dumps the process stack and process state for the current process.
  
• oradebug tracefile_name
  Writes the identifier to the trace file.