JDBC connection failing with ORA-12516

Question

I have some unit tests that talk to Oracle-11g XE. Running each test individually works fine, but when I run them all, the first few pass, and the rest fail with this error when trying to create the DB connection:

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

This happens no matter what order I run the tests in, so the problem seems to be that I'm using up some kind of resource on the DB server.

I tried running the tests slowly, and checking the # of connections in use during the run with:

select count(*) from v$session;

The # of sessions always hovers between 26 and 28, and the limit is set to 49, so I'm not sure what the problem is.

The problem also gets worse if I run the tests quickly. When I run them without any added delays, 90% fail with this error. I put a sleep in tearDown() to get a better idea of what's going on, and in that case only about 10% fail.

Answer 1

You probably need to increase the value of the PROCESSES parameter. Also do a "lsnrctl servcies" command to see if the service is showing up as blocked. I recall a similar issue where the listener is being smart and counting the number of connections being requested for the service. The listener doesn't learn about the disconnects until PMON notifies it. And that can be 5 or 10 minutes later. So the listener thinks you have a lot of open connections and decides to be proactive and not hand out anymore connections -until it is told otherwise by PMON. If you increase the PROCESSES parameter, you might be able to bump yourself up to the number of connections you might make in the window.

UPDATE: This link describes it: https://forums.oracle.com/forums/thread.jspa?threadID=360226