Re: Open cursor error

As Andy mentioned you probably have bugs in application code in Java. As I see in our databases the max open_cursor currently shows as <50 and we do have applications which are batch having dedicated type connections and also other ones that use connection pool too. As you mentioned in your case, the majority of sessions showing open cursor count as 300+ and the top one which you killed was already having 900+ so it looks higher to me. But yes every application is different so maybe others can comment on this figure though. However, as per my understanding , a JDBC resultSet is supported by a single cursor on the database. When close() is called on the ResultSet, the cursor is released and this close() should always be there in the final block always without a miss.

However, I am not sure in the case of connection pool type of applications when connections released to pool and already have some of the open cursors from the previously served functionality, then the new invoke of the same connection/session will keep on adding to the open cursor count and can cause such failures. Again others can comment.

On Sat, Aug 12, 2023 at 2:48 AM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you so much for the response.
>
> I can understand in plsql code when we define explicit cursors it has to
> be closed or else it can cause increase in open cursor count. And as you
> mentioned in exception blocks it has to be closed without miss. And I agree
> that could be easily be missed in many cases by the developer.
>
> However as I mentioned in our cases this session was submitting individual
> sqls directly from Java code but not from/through any plsql blocks or
> procedures. So I was wondering what kind of code in Java can cause such
> open cursor issue? Can you please guide me to some examples?
>
> And also during the issue period I saw almost all the session from the
> application(from jobs thin client program) were having open cursor count
> 300+ and top one was showing 999(which we killed). Are these number of open
> cursor count for individual sessions, normal or as you said there is
> definitely a cursor leak bug in the Java code?
>
> On Fri, 11 Aug, 2023, 7:17 pm Andy Sayer, <andysayer_at_gmail.com> wrote:
>
>> You have a cursor leak bug in the code that used the session. Your job
>> was reusing that session.
>>
>> I find that most of the time, cursor leaks come from explicitly opening
>> and closing cursors in the main code body but not handling open cursors
>> when exceptions are raised.
>>
>> Thanks,
>> Andy
>>
>> On Fri, Aug 11, 2023 at 3:09 AM, yudhi s <learnerdatabase99_at_gmail.com>
>> wrote:
>>
>>> Thank you.
>>>
>>> I was tring to see how the "opened cursors" count is increasing for the
>>> session, so i tried running the simple statement as below by connecting to
>>> a sql plus session.
>>>
>>> Create table test (c1 varchar2(4000));
>>>
>>> Insert into test values('AB');
>>> Insert into test values('CD');
>>> Insert into test values('EF');
>>> Insert into test values('XX');
>>> Insert into test values('YY');
>>>
>>> Then running the stats from gv$sesstat doesnt show a increase in the
>>> 'opened cursors current' after each Insert/DML statement irrespective of
>>> COMMIT. Which means its not true that each DML in a session caused a "open
>>> cursor" for that session.
>>>
>>> So it seems this stats "opened cursors current" depends on the normal
>>> explicit CURSOR declaration in which we have to open and then close the
>>> cursor explicitly in plsql block/procedure. But then I am wondering how its
>>> caused the query submitted directly from Java to have those 1000+ "open
>>> cursor"?
>>>
>>> Regards
>>>
>>> Yudhi
>>>
>>> On Fri, 11 Aug, 2023, 1:18 am Lok P, <loknath.73_at_gmail.com> wrote:
>>>
>>>> Can't answer why killing the top session helped fixing the issue.
>>>> However I believe each SQL statements creates a cursor in memory and would
>>>> be closed implicitly once it's done. I am not sure if it's kept open till
>>>> commit happen.
>>>>
>>>> But as you mentioned the SQL statement or the session which was
>>>> consuming 999 open cursor was coming on top was from Java but not any plsql
>>>> procedure. So it may be something like opening the connection but not
>>>> closing etc.
>>>>
>>>> On Thu, 10 Aug, 2023, 12:50 am Pap, <oracle.developer35_at_gmail.com>
>>>> wrote:
>>>>
>>>>> Hello Experts,
>>>>> We have this database with Oracle version 19C.We encounter below error
>>>>> Ora-01000 for one of the application jobs even though we have not changed
>>>>> anything in the code recently. Then as this parameter "open_cursors"
>>>>> description says "max # cursors per session" and we have set it as ~1000.
>>>>> We thought , by just rerunning the job may make the job succeed , as
>>>>> because this limit was for the specific SESSION and killing and rerunning
>>>>> the job might grab a new session and thus will make the job succeed. But
>>>>> rerunning again made the job fail with the same error.
>>>>>
>>>>> ORA-01000: maximum open cursors exceeded
>>>>>
>>>>> Then we ran the below query on "GV$*" views and found one of the top
>>>>> sessions was showing the 'opened cursors current' VALUE as ~999 and other
>>>>> followed sessions were having values in the range of ~300. And that
>>>>> specific top session was showing as "inactive" though in v$session but the
>>>>> sql it was showing from a different job altogether. But still we killed
>>>>> that session. And then reran the job which was failing with that Ora-01000
>>>>> and surprisingly that succeeded.
>>>>>
>>>>> So I have a few questions here,
>>>>> 1)As this parameter "open_cursors" is a limit for a specific session
>>>>> but not at the database level, So how come killing an existing session
>>>>> makes the new session/job successful?
>>>>> 2)Though we have the query which the top session was pointing to, that
>>>>> was inactive, and was directly submitted from Java code and also that code
>>>>> was not changed recently. So how can that query cause Ora-01000? Or Is
>>>>> there any other way to debug and find more info for the root cause and fix
>>>>> for the Ora-1000 error from the AWR/dba_hist* views? Want to ensure this
>>>>> error should not appear again in future.
>>>>>
>>>>>
>>>>> SELECT a.VALUE,
>>>>> s.username,
>>>>> s.sid,
>>>>> s.serial#
>>>>> FROM gv$sesstat a, gv$statname b, gv$session s
>>>>> WHERE a.statistic# = b.statistic#
>>>>> AND s.sid = a.sid
>>>>> AND b.name = 'opened cursors current'
>>>>> AND b.inst_id = s.inst_id
>>>>> AND s.inst_id = 1
>>>>> AND s.inst_id = b.inst_id
>>>>> order by a.value desc
>>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l