Andrew Fraser DBA

Oracle Orphaned Processes

Error reported to application attempting to connect to database:

ORA-12516, TNS:listener could not find available handler with matching protocol stack

Underlying cause (this reported in database alert log rather than at application side) was:

ORA-00020: maximum number of processes (nnn) exceeded

Fix was to kill a large number of orphaned processes, with no associated session, using below sql.

Reference How to Identify Orphan Database Processes (Doc ID 2580865.1) – however note that sql used in my case (below) differs from the sql given in that doc.

SELECT 'ps -f ' || p.spid AS ps
     , 'kill -9 ' || p.spid AS kill9
     , p.* 
  FROM v$process p
 WHERE NOT EXISTS ( SELECT NULL FROM v$session s WHERE p.addr = s.paddr )
   AND p.pname IS NULL
   AND p.program != 'PSEUDO'
   AND p.background IS NULL
 ORDER BY p.spid
;