How many cursor can be opened in Oracle?

I have a question regarding open cursors and v$open_cursor. We have a
Java client application that sits on an Oracle database (8.0.4). All
database activity is through the call of stored procedures (via JDBC).
The instance is shared with other users and applications too.

We are hitting the max_open_cursors of 50. We are not explicitly
declaring our own cursors in the stored procedures, so it's not as if we
forgot to do a close for every open.

My question is: How does Oracle do cursor "garbage collection"? It seems
like there are some cursors hanging around. Is there anyway to hasten
the deallocation of an implicitly created cursor? Or is this some Oracle
internal action that we cannot control? How long does Oracle keep a
cursor?

I know I can increase max_open_cursors, but what's to say we won't have
the same problem with a value of 500?

Thanks.

Jim

Ans:

Look up the init.ora parameter:

close_cached_open_cursors boolean FALSE

plsql keeps them around for performance. change that to get them to go away. I
would opt to up max_open_cursors rather then close the cached open ones if you
do tend to reuse them in a session tho.

Thomas Kyte
[email protected]

Hosted by www.Geocities.ws