Forums Register Login

java.sql.SQLException: ORA-00020: maximum number of processes (%s) exceeded

+Pie Number of slices to send: Send
Hi, I have an application that pulls Oracle data onto a web form. In our test environment that Oracle db only allows 38 processes. I'm finding that after an hour or so of testing I receive the error above which seems to imply that I'm not completely closing the connection ... but I think I am closing everything...
My data access class has a constructor that opens a db connection and statement object. And my finalize calls a method of the data access class that closes everything up. I've printed log statements that show that the statement and resultSet objects are closed when all is done and that the driver is deregistered. Does anybody know what would cause this error?
Here's an excerpt from my constructor (DRV, CON and ST have class scope):
String url = "jdbc racle ci:@myDBName";
String driver = "oracle.jdbc.OracleDriver";
Properties p = new Properties();
DRV = (Driver) Class.forName(driver).newInstance();
p.put("user", "xxxxx");
p.put("password", "xxxxx");
CON = DRV.connect(url, p);
ST = CON.createStatement();
and an excerpt from my finalize:
RS.close();
ST.close();
DriverManager.deregisterDriver(DRV);
If anyone can tell me what I'm doing wrong (or help me to better understand what Oracle means by an open process) I would GREATLY appreciate it! Thanks.
+Pie Number of slices to send: Send
Hi Patti,


Oracle db only allows 38 processes


This seems quite low to me. I think the default value is 100. I suppose increasing the number is not an option, right?


an excerpt from my finalize


If you are "close()"ing your "ResultSet"s and "Statement"s in a "finalize()" method, that means that the "close()" is happening only when the garbage collector decides to clean up the object(s). This may not be happening (or not happening when you think it should).
By the way, I didn't see any code that "close()"s the "Connection". Are you sure that 'deregistering' the "Driver" is sufficient?
Once you have finished with a "ResultSet" and "Statement", you can safely "close()" them. I usually do this in a "finally" block (and not a "finalize()" method).
Good Luck,
Avi.
+Pie Number of slices to send: Send
Thank you for your quick response. I am calling the close() methods in my finally block -- it's just that my finally block calls a method of my data access class to clean everything up. But I wasn't calling a close() on the connection object (what a goober!!) and I'm sure that's the problem. Before I can confirm I have to figure out how to clear the Oracle processes that are still hung a day later. Good grief. Anyway, thank you very very much.
+Pie Number of slices to send: Send
Avi,
Just to follow-up - deregistering the drivers wasn't closing the connection. A CON.close() fixed my problem. Thank you so much.
+Pie Number of slices to send: Send
 

Originally posted by Patti Steiner:
Avi,
Just to follow-up - deregistering the drivers wasn't closing the connection. A CON.close() fixed my problem. Thank you so much.


closing the connection is the sure way to eliminate this error, but the way you're going about it worries me a little. Your goal should be to close all JDBC resources AS SOON AS POSSIBLE!! By leaving it until your program exits is leaving unneeded and valuable JDBC resources tied up. This makes resources unavailable for someone else to use, bogs down the database and is prone to many errors like the one you experienced.
A database is like a power source. Connections are like an extension cord to acces the power from applications. There are a limited number of plugins for the connections to connect to the database, so use when you are done using them, unplug them and allow other people to use the plugin. Statements are like lamps plugged in to the extension cord. It draws power from the power source through the extension cord. If you have too many lamps on, the power source runs out of energy and 'crashes'. The ResultSet is like the lightbulb in the lamp. It can't make light without the lamp and (in this case) you can only get have a single lightbulb in every lamp.
And never return a resultset from a method( don't know if you're doing this, but it usually goes hand in hand ):

now you've trapped yourself. You can't close the statement, because it will close the corresponding resultset, losing all that data the returning method is expecting. You are forced to keep statements open until you close the connection, which after time will eat up your resources and cause problems.
Anyways, this may or may not have been helpful, but all I'm trying to say ( in a longwinded way ) is to close your resultsets and statements in scope as soon as possible.
Jamie
+Pie Number of slices to send: Send
Jamie,
Thank you for the additional information. I'm new to using java for database connections and everything you said was really helpful. It's funny you mentioned that I shouldn't return a ResultSet from a method -- I was originally doing that but figured out pretty quickly that it was a problem. Thanks again.
It's weird that we cook bacon and bake cookies. Eat this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 4798 times.
Similar Threads
Database Access Design
JDBC connection with oracle 10g
java.sql.SQLException: Io exception: The Network Adapter could not establish the conn
Difference between JDBC in 'normal' Java and JSP?
The Network Adapter could not establish the connection in jdbc connection
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 02:40:00.