How can I troubleshoot which processes are keeping connections open?

Background: A QA installation of several software components is generating an ORA-12516 error now and then, and I'm not sure which component is at fault. The DBA raised the value of PROCESSES and things are working fine, but I'd like to understand where the surprising number of connections is coming from.

(Am I keeping some open when I shouldn't? Is there a rogue process in the QA environment? Etc.)

Assuming I'm not able to instrument the clients which open up connections, can I interrogate the database or its logs? If so, how?

  • And you are certain your parameters for Session and Processes in the init.ora (or spfile) are sufficient for all of your concurrent connected clients? – REW Jun 27 '12 at 15:46
  • @REW, well, in a sense that's what I'm trying to determine. :) I'd estimate no more than 50 or so concurrent client conns, and the DBA said the old ceiling was 150 connections. – pilcrow Jun 27 '12 at 16:04
  • 5
    You can start with this: select username, count(username) from v$session group by username order by count(username); – Leigh Riffel Jun 27 '12 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.