Too Many Open Cursors problem with Oracle

If your application creates too many concurrent connections to a database it could encounter a problem when more cursors are required than are allowed open. This sometimes happens when you have a database fetching problem with an ORM framework such as Hibernate that is creating an N + 1 fetch. It can also happen if your transaction is not atomic enough.

The best solution to this problem is to tackle the design of the application. However having a larger number of open cursors apparently does not affect the efficiency of retrieval so increasing the number has its advantages.

The following 2 scripts would help to understand and see what is going on in the database:

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

While the following script would change the number of open cursors allowing by the database:

alter system set open_cursors=300


2 Comments to “Too Many Open Cursors problem with Oracle”

  1. Thanks! I was getting this error due to a bug in Hibernate, not releasing connection resources.
    Its much better to do this in Perl though.

  2. I found that I was having this problem running a batch of unit tests in my sping – hibernate app.
    The individual tests work fine. Just fail when I run all together. As a work around, in the @Before (setup) method of the test I do a get the current session from the session factory and call the clear() method. This seems to sort it out..

Leave a Reply