Open Cursor Exceeded in Oracle

[Muhammad Ijaz]

We are using the ORACLE A.S 10g and D.B 10g (10.0.1). We maintain a pool of D.B connection on A.S. Our application is web based. We are properly closing the resultset and statement on java side. But we often receive the error open cursor exceeded. Currently on production we set the limit to 5000.
We use following query to check the currently open cursor

select a.sid, a.value, b.name, b.statistic#
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and a.sid = 555(any sid number)

The count of open cursor is increasing it cannot be reduce until we restart our application server.
Because we are using the connection pooling that is why cursors are not closed?
Please advice us what should we do. What other areas should we look.

Thanks

[Paul Sturrock]

Do you close all connections in code in a finally block? The connection pool cannot release them otherwise.

[Tim McGuire]

Muhammad Ijaz wrote:We are using the ORACLE A.S 10g and D.B 10g (10.0.1). We maintain a pool of D.B connection on A.S. Our application is web based. We are properly closing the resultset and statement on java side. But we often receive the error open cursor exceeded. Currently on production we set the limit to 5000.
We use following query to check the currently open cursor

select a.sid, a.value, b.name, b.statistic#
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and a.sid = 555(any sid number)

The count of open cursor is increasing it cannot be reduce until we restart our application server.
Because we are using the connection pooling that is why cursors are not closed?
Please advice us what should we do. What other areas should we look.

Thanks

Your application is leaking resources. Check to make sure you close connections and cursors when you are done with them.
Your query will not show an accurate count of open cursors.
see this for further reading:
http://www.orafaq.com/node/758