aspose file tools*
The moose likes JDBC and the fly likes  java.sql.SQLException: ORA-00020: maximum number of processes (%s) exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark " java.sql.SQLException: ORA-00020: maximum number of processes (%s) exceeded" Watch " java.sql.SQLException: ORA-00020: maximum number of processes (%s) exceeded" New topic
Author

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

Patti Steiner
Greenhorn

Joined: Oct 28, 2003
Posts: 4
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.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

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.
Patti Steiner
Greenhorn

Joined: Oct 28, 2003
Posts: 4
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.
Patti Steiner
Greenhorn

Joined: Oct 28, 2003
Posts: 4
Avi,
Just to follow-up - deregistering the drivers wasn't closing the connection. A CON.close() fixed my problem. Thank you so much.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Patti Steiner
Greenhorn

Joined: Oct 28, 2003
Posts: 4
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.SQLException: ORA-00020: maximum number of processes (%s) exceeded