Comments below ... ----------------- Ron Reidy Lead DBA Array BioPharma, Inc.
<snip> OK, but in the thread you posted from asktom.oracle.com, the response to the poster was: "v$open_cursor can be misleading -- it is cursors that have been opened at some point and may (or may not be) still open. It is useful in helping to track down cursor leaks -- but it shows you more then just "really truely open" cursors." and later... "The issue is v$open_cursor will show you MORE open cursors then you really have (due to some cursor caching done by plsql and other environments). You need to goto v$sessstat to get the REAL currently opened number of cursors. There may be cursors in v$open_cursor that are not counted in the v$sesstat view for stat #3, they will be really closed if we need those slots again." I'm not citing that article to prove you wrong, but because I don't know anything at all about Oracle and am getting confused by reading different articles that seem to say slightly different things. [rr] I was testing on a DB instance where I was the only user at that time. I did not bother to check v$sesstats (I probably should have), but I will leave this to your testing. So my question is, when Oracle tells me I've exceeded the maximum cursors limit, am I exceeding a limit of "really open" cursors or a limit of "have been opened" cursors? [rr] From the 9.2 docs: OPEN_CURSORS Parameter type Integer Default value 50 Parameter class Dynamic: ALTER SYSTEM Range of values 1 to 4294967295 (4 GB -1) OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed. > When I called disconnect() on my database handle, the cursors were > closed. > > I have searched metalink, google, and otn for some reference on this > issue and found nothing that indicates there is a way to close a ref > cursor object prior to disconnect. What about this: http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Binding_Cursors At the end of the "Binding Cursors" section it says that cursors need to be (and can be) closed with code like this: $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;"); $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } ); $sth3->execute; That's where I got one of the methods I've tried for closing the cursor. I've also had other people who know Oracle but not Perl tell me there's no way that should work, but it's in the docs. So that's why I'm confused. Some people tell me I can't close my cursors this way, others say that I can't close cursors at all, and others say that cursors get closed automatically by the Perl garbage collector. Is anyone able to shed some light on the situation? Thanks! Mike This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
