• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

ORA-01000: maximum open cursors exceeded

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Prakash Chicka
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Prakash Chicka
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Prakash,
Can you try turning on monitoring (to see if there is a resource leak) and tracing (for more details) in production?
 
Ranch Hand
Posts: 1066
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I did have the same problem. It was a static variable that was reused in different queries.

for example:
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 = jdbc:oracle: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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"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
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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());
}
}
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic