Re: OpenCursors - ORA-1000

In article <37c1471f.350362043_at_newshost.us.oracle.com>, Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>On Fri, 20 Aug 1999 11:43:10 +0100, Thomas Kunetz wrote:
>>I have a java server which requests an Oracle database. Sometimes I have
>>this Error:
>> java.sql.SQLException: ORA-01000: maximum open cursors exceeded
>>but I close all the statements and resultsets.
>>Thanks for your help.
>>Tom.
>
> you can select that value from v$parameter.
> it is settable in the init.ora. I set mine to about 1000 for example.
>
> You most likely are missing some cursors (letting them go out of
> scope, closing the result set but not the statement and so on). to
> find these, dump the v$open_cursor table for your session evernow
> and then from another session (select sql_text from v$open_cursor
> where user_name = 'YOUR APPS USERNAME' for example) and track down
> the queries that are really not being closed.

Some Oracle applications (Forms, for example) leave cursors open in their automatic processing, especially LOV's, and the Oracle-released default value of 50 is never enough to run Forms of significant complexity. We have checked here, and none of our explicitly written cursors are open when we get the ORA-01000 message. It has been some time since I checked, but I think that even cursors explicity closed can show up in the list of open cursors, so it the connection between closing a cursor and the count of open cursors may not be as straightforward as one would expect.

Steve Cosner Received on Sun Aug 22 1999 - 15:08:16 CDT