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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 于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
