I'd like to find out if there are any open_cursors not being closed as of now by viewing data dictionary tables or views. I tried v$sysstat and v$open_cursors without any help. They only show the cursors opened and closed accumulatively.
Any help would be appreciated.
Re: how to find out whether a cursor has been closed
The cursors listed in V$OPEN_CURSORS are,in fact, still open.This is a performance optimization method used by the server.Even though you have CLOSED the cursors, they are still cached in Server memory. This is not a bug. The code leaves cursors open and caches them, as intended.The CLOSE_CACHED_OPEN_CURSORS parameter in init.ora forces all of these cached cursors to be closed at the end of each transaction,if that is desired.This parameter is obsolete in Oracle versions 8i and above.
Thanks for replying.
My developers ask me if I can find the unclosed cursors for them. I told them to go through their codes. But I still want to know if there is any way to find out in oracle if there is any open cursors which should have been closed but still open. Thanks anyway.
Not feasible YOU find the cursors mistakenly left open (to be true, this is a bug, and bug removal MUST be left to programmers), BUT you can get the truely number of current open cursors with the statistic of opened cursors current , as showed in http://asktom.oracle.com/pls/ask/f?p...D:553222846752 , hence identifying the cursor leaking programs, BUT the programmers WILL need open, find & correct the bug.