aspose file tools*
The moose likes Oracle/OAS and the fly likes ORA-01000: maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "ORA-01000: maximum open cursors exceeded" Watch "ORA-01000: maximum open cursors exceeded" New topic
Author

ORA-01000: maximum open cursors exceeded

Prakash Chicka
Greenhorn

Joined: Mar 09, 2005
Posts: 23
Hi All,
Could any one help me on this as its very urgent and the application has been removed from the production because of this problem.

We went through the code just to make sure if there are any connections/resultsets/statements are left openend. But we have taken care to close all connections, resultsets and statements and its been done in the standard format.

public boolean closeConAndStatement(Connection con, PreparedStatement prepareStmt, ResultSet resultset)
{
try
{
if (resultset != null)
resultset.close();

if (prepareStmt != null)
prepareStmt.close();

if (con != null)
con.close();

resultset = null;
con = null;
prepareStmt = null;

return true;
}
But still i dont know why the hell this problem is happening.

Server details:
1. Oracle database server
2. WAS 4.0.4
Connection pool settings in WAS: min=1 and max=30
open_cursors=300 in init.ora file

The application runs fine for some days and then starts giving this problem.
The open_coount has reached to 150.

Also it would be great if explain the difference b/n cursor and connection.

Thanks in advance,
Prakash
[ May 17, 2006: Message edited by: Bear Bibeault ]
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

You usually want to put the close code inside a finally clause, that way if something else fails, it will still run the close code. It looks like you have the close code in the try part.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Prakash Chicka
Greenhorn

Joined: Mar 09, 2005
Posts: 23
yes its been closed in the finally block only.....all that part is very fine...please let me know if there are any settings in oracle level or WAS level can help us to solve this problem...
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Prakash,
In my opinion, you're going to have to debug it via the Oracle "V$" views, like "V$OPEN_CURSOR". Please refer to the "Oracle Database Reference" (part of the Oracle documentation) for details on the "V$" views. For your information, the Oracle documentation is available from:

http://tahiti.oracle.com

Good Luck,
Avi.
Prakash Chicka
Greenhorn

Joined: Mar 09, 2005
Posts: 23
Hi Avi,
Thanks for your help. I have executed my this query many times and also we can see some of our our queries holding the cursor. But we are closing the connections/resultsets/statements properly after executing the query. Please let me know if there is way, i can find out that eiter application server or the oracle server are not releasing the cursors properly.

Also the client says, there are more than 100 apps using the same Instance and they are not facing the problem. Is it possible for other applications to get the cursor when it says, ORA-01000: maximum open cursors exceeded for the other application which is using the same instance.

Regards,
Prakash
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Prakash,
Are you saying that you have already looked at the "V$" views, and that you can see that there are open cursors (which should be closed)? If so, then it seems that you are not closing all the "ResultSet"s, "Statement"s and "Connection"s in your code.

I don't use WAS, so I can't help you, but I imagine there may be some way to run it in "debug" mode. I work with OC4J and you can run it in debug mode by setting some "System" properties when starting up OC4J. Perhaps there is something similar in WAS. Do you know? Have you tried?

Regarding Oracle, you can set tracing on in the database. I'm only going from memory, but you can do something like:

Please check the Oracle documentation for the correct syntax (and more details).

You may also be able to incorporate P6 Spy into your application. That may also help you to track down your problem[s].

As far as I know, the "maximum cursors" is a per-session limit. In other words, each session can have 300 open cursors. Again more details are available in the Oracle documentation. However, since you are using WAS, you are probably using a connection pool, so you may have several users using the same database session, so if you have a lot of concurrent users all using the same session, I guess you could reach your open cursors limit quite easily.

Good Luck,
Avi.
Prakash Chicka
Greenhorn

Joined: Mar 09, 2005
Posts: 23
Hi Avi,
Thanks lot for your reply. The problem is happening in the production server (may be tomorrow they will take our app out from production) and they have executed the query and sent me the output. According to that, our application has opened 160 cursors.
I have been telling them that, there are many ways they can tune the database to overcome this problem. Since I am not a DBA, I dont know where exactly we can tune it in the databse.Also they are not ready to trace it and give us the inputs.
Strange thing they are saying that "There are more than 100 Apps. using this Instance and they are not facing such problem". I just cant beleive this as cursors will be shared across the applications.

It would be great if anybody can give me the exact solution in the oracle level or WAS level, so that I can ask them to do the same.

Regards,
Prakash
luo hao
Greenhorn

Joined: Jun 13, 2005
Posts: 2
Hello
I im facing the same problem.
I use websphere4.0.

But now I can give some research result even I have not solved the problem yet.

1.In websphere4.0, I use connectin pool. If I use statement, then there
is not any problem. If I use preparedStatement, the I encounted the same
problem. In the v$open_cousor, there are many records even I have closed
every preparedStatement.

2.In tomcat4.0-..., I do not use connection pool, then there
is not any problem. Even I do not close the statement and preparedstatement.
luo hao
Greenhorn

Joined: Jun 13, 2005
Posts: 2
J2EE container would do something for preparedStatement, that is setting
the preparedStatement Pool. Websphere4.0 can do that, and the initial preparedStatement pool size is 100. So even in application the preparedStatement has been closed, but that is only indicate that it has been returned to the preparedStatement pool. If we set the preparedStatement
pool size to 0, then in the V$open_cousor table, no cousor would be found.
FRabelo
Greenhorn

Joined: Jul 15, 2004
Posts: 1
Hi All,

I had the same problem.

This problem only occurs in the methods where I use more of the one instruction "stmt = con.prepareStatement( sql );" .

I have solved this problem closing all the PreparedStatements before being reused inside of the method.

Example:

public void example() throws Exception{
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
con = getConnection();

//-- CREATE NEW PREPAREDSTATEMENT
stmt = con.prepareStatement(sql.toString());

..............................
rs = stmt.executeQuery();
..............................

//-- CLOSE DE PREPAREDSTATEMENT BEFORE REUSE //
//-- THIS CLOSE THE OPEN CURSORS FOR THIS STMT //

try{stmt.close();}catch (Exception ex){}

//-- NEW PREPAREDSTATEMENT //
stmt = con.prepareStatement(sql.toString());

rs = stmt.executeQuery();

// AND CLOSE FOR NEW REUSE.
try{stmt.close(); }catch (Exception ex){
}
................................

Good Luck,
Rabelo.


Originally posted by luo hao:
Hello
I im facing the same problem.
I use websphere4.0.

But now I can give some research result even I have not solved the problem yet.

1.In websphere4.0, I use connectin pool. If I use statement, then there
is not any problem. If I use preparedStatement, the I encounted the same
problem. In the v$open_cousor, there are many records even I have closed
every preparedStatement.

2.In tomcat4.0-..., I do not use connection pool, then there
is not any problem. Even I do not close the statement and preparedstatement.


-----------------<br />Flavio Rabelo<br />SJPC 1.4<br />Brazil<br />-----------------
Prakash Chicka
Greenhorn

Joined: Mar 09, 2005
Posts: 23
Mark,
Thanks for your help but still we are not able to figure out the root cause. we cant expreriment each time as the application is in production and we are not able to reproduce the same in our test environment.

Regards,
Prakash
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

Prakash,
Can you try turning on monitoring (to see if there is a resource leak) and tracing (for more details) in production?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Vishwa Kumba
Ranch Hand

Joined: Aug 27, 2003
Posts: 1064
Which Oracle database version are you using?

We had a similar problem with Oracle 8i. One of our friendly 3rd party supplied us a set of classes (jar file) which we were using in our application. We had to decompile their code to find out the problem.
They didn't even have a single preparestatement.close() in their code!. Though this problem was identified and fixed by the 3rdy party a long time back, Our friendly release team had applied the wrong version of the jar file in the production env! and hence we couldn't reproduce the problem in our test envs!

My suggestion would be to check the software/application verion differences between live and test envs!
suprit chaudhary
Greenhorn

Joined: Aug 04, 2005
Posts: 1
All EJB app servers use pool DataSource driver to connect to the databse.
.ie "oracle.jdbc.pool.OracleConnectionPoolDataSource"
This is the point of trouble.

I had the same problem.

I get ride of pool datasource driver and went with old way of doing it.
ie. simple jdbc driver from sun.
"DriverManager.getConnection(dbUrl, _databaseUserName, _databaseUserPassword);"

And this fix the problem. Since I used simple jdbc driver from sun, I never
had any problem so far.

If you need a pool of connection, write your own pool using sun jdbc connection.

TIP: create the number of connection you want to keep in pool, then add them in vector. Write little manager to handle the connection sharing.

This works. In most of the case, to be honest to will not really need even a pool.

If you need help about writing connection pool from sun jdbc connection driver drop me email. supritchaudhary@yahoo.com
-Suprit
John Coehn
Greenhorn

Joined: Sep 15, 2005
Posts: 1
I did have the same problem. It was a static variable that was reused in different queries.

for example:
Ashutsh Pitre
Greenhorn

Joined: Sep 23, 2003
Posts: 5
Hi ,

I am also facing error "Max cursor exceeded" in production environment.

The same application code was working well in WAS4, however, after migration to WAS5.1, the error started coming up every alternate day.

We have made it sure that all RS, STMT and connection objects ae closed in finally block.

To take preventive meausure, we are bouncing the server every night to reduce the cursor count to zero.

Details
JDBC Provider
class - oracle.jdbc.pool.OracleConnectionPoolDataSource
oracle driver - classes12.zip
oracle driver version - 9.0.2.0.0
URL = jdbcracle:thin:@wpshXXXX.win.XXXXXX.com:1521:wXX5
Minimum pool size = 10
Maximum pool size = 100
Connection time out = 180 s
Idle time out = 1800 s
Orphan time out = 1800 s
Statement cache size = 1000 (for wepod instance)
Disable Autoconnection cleanup = unchecked
OPEN_CURSOR Parameter - 400

Any pointer towards solution is appreaciated!

Can anybody let me know how do I decide the value for parameter "Statement cache size" @ WAS side and OPEN_CURSOR @ DB side

Thanks and regards,
Ashutosh
925 577 6482
sbalakri
Greenhorn

Joined: Dec 09, 2005
Posts: 1
The Statement Cache Size was a the node level (for all connections of a DataSource) in Websphere 4.0. In 5.1 it is a the connection level.

Hence arrive at the right value by dividing the previous setting (Websphere 4.0) by the number of connections.

If you have a high Statement Cache Size setting you are prone to run into
Cursor Count Exceptions at the database .
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

"sbalakri",
"Books",
Welcome to JavaRanch!

We're pleased to have you here with us in the Oracle forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks,
Jeanne
Oracle Forum Bartender
SuprajaY Yasoda
Greenhorn

Joined: Oct 17, 2005
Posts: 17
hi am also getting the same error for this below code, can some pls help me
try {
//get the connection
conn = CommonUtil.getConnection();

psSupp = new StringStatement(conn, strTP);
psSupp.setString(1,strITTCode);
psSupp.setString(2,strResponseNo);

Log.out.debug(psSupp.toString());

rsSupp = psSupp.executeQuery();

if (rsSupp.next()) {
udSupplierBriefInfo.setStrSupplierType("0");
udSupplierBriefInfo.setStrSupplierCode(rsSupp.getString("SUPP_CODE"));
udSupplierBriefInfo.setStrSupplierSiteId(rsSupp.getString("SUPP_SITE_ID"));
udSupplierBriefInfo.setStrSupplierName(rsSupp.getString("SUPP_NAME"));
udSupplierBriefInfo.setIntResponseNo(Integer.parseInt(strResponseNo));

if (rsSupp != null) rsSupp.close();
rsSupp = null;
if (psSupp != null) psSupp.close();
psSupp = null;
if (conn != null && !conn.isClosed()) conn.close();


}
else {
conn = CommonUtil.getConnection();
psSupp = new StringStatement(conn, strNonTP);
psSupp.setString(1,strITTCode);
psSupp.setString(2,strResponseNo);

Log.out.debug("Not a TP that is logically undeleted -> Next querry :" +psSupp.toString());

rsSupp = psSupp.executeQuery();

if (rsSupp.next()) {
udSupplierBriefInfo.setStrSupplierType("1");
udSupplierBriefInfo.setStrSupplierCode(rsSupp.getString("SUPP_CODE"));
udSupplierBriefInfo.setStrSupplierSiteId(rsSupp.getString("SUPP_SITE_ID"));
udSupplierBriefInfo.setStrSupplierName(rsSupp.getString("SUPP_NAME"));
udSupplierBriefInfo.setIntResponseNo(Integer.parseInt(strResponseNo));

if (rsSupp != null) rsSupp.close();
rsSupp = null;
if (psSupp != null) psSupp.close();
psSupp = null;
if (conn != null && !conn.isClosed()) conn.close();

}
/*else {

psSupp = new StringStatement(conn, strNonTPDeleted);
psSupp.setString(1,strITTCode);
psSupp.setString(2,strResponseNo);

Log.out.debug("Not a TP or Non-TP that are logically undeleted -> Next querry :" +psSupp.toString());

rsSupp = psSupp.executeQuery();

if (rsSupp.next()) {
udSupplierBriefInfo.setStrSupplierType("1");
udSupplierBriefInfo.setStrSupplierCode(rsSupp.getString("SUPP_CODE"));
udSupplierBriefInfo.setStrSupplierSiteId(rsSupp.getString("SUPP_SITE_ID"));
udSupplierBriefInfo.setStrSupplierName(rsSupp.getString("SUPP_NAME"));
udSupplierBriefInfo.setIntResponseNo(Integer.parseInt(strResponseNo));
}*/
else {
conn = CommonUtil.getConnection();
psSupp = new StringStatement(conn, strTPDeleted);
psSupp.setString(1,strITTCode);
psSupp.setString(2,strResponseNo);

Log.out.debug("Not a TP that is logically undeleted or Non-TP -> Next querry :" +psSupp.toString());

rsSupp = psSupp.executeQuery();

if (rsSupp.next()) {
udSupplierBriefInfo.setStrSupplierType("0");
udSupplierBriefInfo.setStrSupplierCode(rsSupp.getString("SUPP_CODE"));
udSupplierBriefInfo.setStrSupplierSiteId(rsSupp.getString("SUPP_SITE_ID"));
udSupplierBriefInfo.setStrSupplierName(rsSupp.getString("SUPP_NAME"));
udSupplierBriefInfo.setIntResponseNo(Integer.parseInt(strResponseNo));
}
if (rsSupp != null) rsSupp.close();
rsSupp = null;
if (psSupp != null) psSupp.close();
psSupp = null;
if (conn != null && !conn.isClosed()) conn.close();

}
}

if (rsSupp != null) rsSupp.close();
rsSupp = null;
if (psSupp != null) psSupp.close();
psSupp = null;
}// end try
catch(Exception ex) {
ex.printStackTrace();
Log.out.error("Exception in method loadSupplierBriefInfo() in AwardDAO : "+ ex);
throw new WebittException("LAD007","Could not get Supplier Brief Info", ex);

}
finally {
try {
if (rsSupp != null) rsSupp.close();
if (psSupp != null) psSupp.close();
if (conn != null && !conn.isClosed()) conn.close();
}
catch(Exception fex) {
Log.out.error("Exception in method loadSupplierBriefInfo() finally block in AwardDAO : "+ fex.getMessage());
}
}
Parikshit Devre
Greenhorn

Joined: May 16, 2006
Posts: 1
Hi,

I have read through all the blogs, but no where did I see anybody recommend changing the init.ora parameter open_cursors.

All who are having error ora-01000 can start by setting the open_cursors parameter in init.ora to a higher value than what is specified currently.

If this is not specified, then you can put add it as

open_cursor = value
Raveesh Badoni
Greenhorn

Joined: May 25, 2012
Posts: 1
This problem mainly happens when you are using connection pooling because when you close connection that connection go back to the connection pool and all cursor associated with that connection never get closed as the connection to database is still open.
So one alternative is to decrease the idle connection time of connections in pool, so may whenever connection sits idle in connection for say 10 sec , connection to database will get closed and new connection created to put in pool.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: ORA-01000: maximum open cursors exceeded
 
Similar Threads
MAximum cursor limit
need help to setup trace for connection pooling in WAS
Using single connection cause memory problems?
Inputs needed writing a tool to resolve Connection leakage problem
ORA-01000: maximum open cursors exceeded