Oracle Maximum Open Cursors Exceeded

[mattbaird]

Hi,
I'm getting the following error when running our extensive unit test suite under jboss 2.4.3:
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Is there anyway to reduce the size of the preparedstatement cache? I think this will solve the problem.

thanks.

[l.g.]

I have this problem too.
I think it happens when I deploy apps many times and
JBoss get new connection every time and don`t disconnected. Maybe I`m wrong...
But every day I have to go to Oracle box and manually kill the processes...

[blackangel]

I was running into exactly the same problem but with
iPlanet application server. So it might be internal
Oracle configuration issue, since JDBC doesn't request
to create cursors explicitely.

[tom_muir]

The ORACLE JDBC driver requires that you close all Statement and ResultSet objects. For example:

PreparedStatement ps = new PreparedStatement("SELECT * FROM EMPLOYEE");

ResultSet rs = ps.executeQuery();

ps.close();
rs.close();

If you don't close these resources, ORACLE experiences a memory leak (i.e. cursors run out).

You can also adjust the number of open cursors in your ORACLE pfile using the attribute:

open_cursors = 1024

Hope this helps. Obviously, it is for BMP.

[blackangel]

Dunno whether increasing of curors is useful. We ran
out of 6000 :)

I'm not sure about whether JBoss closes resultsets,
but closing of prepared statements is not done, but
they are also reused and never created twice.