Home » SQL & PL/SQL » SQL & PL/SQL » How to find Open Cursors
How to find Open Cursors [message #190801] Fri, 01 September 2006 05:40 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

I would like to know if we can get the list of open cursors that are unncessarily residing in memory because they were not closed in the code. What data dictionary of ORACLE actually holds this information.

Thanks
Re: How to find Open Cursors [message #190832 is a reply to message #190801] Fri, 01 September 2006 08:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If Oracle could provide a list of unnecessary open cursors, don't you think they wouldn't have closed them already?
Re: How to find Open Cursors [message #190899 is a reply to message #190832] Sat, 02 September 2006 01:22 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hmm.......would like to clarify my point.

Web applications frequently connect to Databases for fetching information and this process is generally achieved by opening some cursors say by running a stored procedure and returning a ref cursor or a normal cursor. I want to during this process, if some of the cursors are left open.

Thanks
Re: How to find Open Cursors [message #190925 is a reply to message #190801] Sat, 02 September 2006 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT * FROM V$OPEN_CURSOR;
Re: How to find Open Cursors [message #190935 is a reply to message #190925] Sat, 02 September 2006 14:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I just wonder. How would you decide which of these cursors can be closed, because they are no longer needed??
Re: How to find Open Cursors [message #190941 is a reply to message #190925] Sat, 02 September 2006 21:50 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

w.r.t your reply, i would like to associate object with the row present in V$OPEN_CURSOR. What i mean is, can we locate the DB object (query etc)that actually opened this cursor through object_id or something. This will help me in finding as to which part of the source code (front end or any of the procedures) opened this cursor and whether it is supposed to be open or not.

thanks
Re: How to find Open Cursors [message #190942 is a reply to message #190801] Sat, 02 September 2006 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>can we locate the DB object
IMO, this is not the correct question.
Is the cost to do so worth the effort?
You seem to be fixated on trying to correct the symptoms; rather than focusing on the real problem; lousy appliaction code.
If the application was well written, then no "dangling" cursors would exist.
By definition, if/when an open cursor exists, Oracle believes a client wants it open & alive.
AFAIK, even if you can correctly identify which cursor(s) "should be" closed; I don't think you will convince Oracle to close it.
Please run some actual tests, post your test code & the results back here for examination & review.

HTH & YMMV
Re: How to find Open Cursors [message #190943 is a reply to message #190942] Sun, 03 September 2006 00:01 Go to previous message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
i am actually handling the back end part of a web application project and wanted to see for my curiosity, if developers by mistake forgot to close the connections/cursors/sqlstatements in their java code.

Anyways thanks for your response.

Previous Topic: does not refresh the job automatically
Next Topic: Getting erroneous record from Bulk insert
Goto Forum:
  


Current Time: Sun Dec 04 22:47:45 CST 2016

Total time taken to generate the page: 0.07434 seconds