Re: OPEN CURSOR EXECEEDED

Hi Krishna,

Every time I come across this error it’s due to a code error causing a cursor leak. Increasing the parameter will only delay the problem as the session is just opening more and more cursors without closing them properly.

You won’t see fragmentation but you will see additional memory use, and you will still hit the same problem.

The good news is that 99% of the time the bug is that cursors are being explicitly opened but only explicitly closed when procedures successfully complete. The code hits some other exception and jumps to its exception handler which does not do anything with the open cursor.

The fixes:
Stop using explicit cursors if you can (implicit ones will implicitly do the right thing). It’s rare you need anything other than this. If you really do need explicit cursors, correct the custom exception handling so that cursors are checked for openness and closed if necessary. Remember that if there is no exception handling block, Oracle will probably be doing the right thing for you.

Start by looking at the plsql which was being called when this error was hit. If I’m right, you’ve got custom exception handling so hopefully you’ve not removed this information from the error stack.

Hope that helps,
Andy

On Wed, 17 Nov 2021 at 04:56, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi Sayan,
>
> Thanks for the information,
>
> We are facing issue of ora-01000 , we are trying to get infirmation about
> historical details , but i guess it wont be there as its session level
> stuff,
>
> Does enabling error stack trace is giod option in production or it can
> cause overhead.
>
> Additonally higher value of open_cursor can cause fragmentation in shared
> pool ?
>
> Regards,
> Krishna
>
>
> On Wed, 17 Nov 2021, 07:07 Sayan Malakshinov, <xt.and.r_at_gmail.com> wrote:
>
>> Just one note: "open_cursors" parameter and ORA-1000 are about "per
>> session" statistics, while 'Current Open Cursors Count' metric shows total
>> number of open cursors for a whole database instance.
>>
>> On Wed, Nov 17, 2021 at 3:33 AM Quanwen Zhao <quanwenzhao_at_gmail.com>
>> wrote:
>>
>>> Hello Krishnaprasad 😊,
>>>
>>> I once wrote a similar SQL script for your requirement last month,
>>> here's the link -
>>> *https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_cocc.sql*
>>> <https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_cocc.sql>
>>> .
>>>
>>> Best Regards
>>> Quanwen Zhao
>>>
>>> Krishnaprasad Yadav <chrishna0007_at_gmail.com> 于2021年11月16日周二 下午2:22写道：
>>>
>>>> Hi Experts ,
>>>>
>>>>
>>>> Currently , we are facing the issue of ORA-1000 open cursor limit
>>>> exceeded , we are looking for any historical value of max open cursor from
>>>> the database . For current utilization we are fetching open_cursor count
>>>> v$sessstat and v$statname .
>>>>
>>>> Request if any inputs we can get on this can be help full .
>>>>
>>>> Regards,
>>>> Krishna
>>>>
>>>
>>
>> --
>> Best regards,
>> Sayan Malakshinov
>> Oracle performance tuning engineer
>> Oracle ACE Associate
>> http://orasql.org
>>
>

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