Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
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

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.