OraFAQ Forum: SQL & PL/SQL » How to find Open Cursors

Home » SQL & PL/SQL » SQL & PL/SQL » How to find Open Cursors

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to find Open Cursors [message #190801]

Fri, 01 September 2006 05:40

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

Report message to a moderator

Re: How to find Open Cursors [message #190832 is a reply to message #190801]

Fri, 01 September 2006 08:39

Frank
Messages: 7901
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?

Report message to a moderator

Re: How to find Open Cursors [message #190899 is a reply to message #190832]

Sat, 02 September 2006 01:22

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

Report message to a moderator

Re: How to find Open Cursors [message #190925 is a reply to message #190801]

Sat, 02 September 2006 10:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT * FROM V$OPEN_CURSOR;

Report message to a moderator

Re: How to find Open Cursors [message #190935 is a reply to message #190925]

Sat, 02 September 2006 14:13

Frank
Messages: 7901
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??

Report message to a moderator

Re: How to find Open Cursors [message #190941 is a reply to message #190925]

Sat, 02 September 2006 21:50

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

Report message to a moderator

Re: How to find Open Cursors [message #190942 is a reply to message #190801]

Sat, 02 September 2006 22:04

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: How to find Open Cursors [message #190943 is a reply to message #190942]

Sun, 03 September 2006 00:01

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 00:43:00 CST 2024