Holding cursors open is a feature. Really. :)
Keeping cursors open for reuse can save a lot of time. Just because you close the cursor in the your program does not mean Oracle will close it also. Have your DBA's session_cached_cursors. This is the number of cursors that will be cached in the PGA of that session. Easy to test. In one sqlplus session, run a PL/SQL block that opens, fetches from and closes a couple of cursors. First: alter session set session_cached_cursors=0; run the PL/SQL Then: alter session set session_cached_cursors=5; run the PL/SQL all the while monitoring v$open_cursor from another session. For further discusstion, take it to an Oracle list. HTH. Jared On Tuesday 11 February 2003 00:29, [EMAIL PROTECTED] wrote: > I explicitly finish() and undef all statement handlers, yes. Those handlers > are in used in small subroutines and as such should be destroy when > returning from those anyway. AutoCommit is on. > > DBA-ers over here can't find much wrong either, it does appear that the > $dbh->do() command uses one cursor which it leaves open (v$open_cursor). > We'll rewrite such commands to use our own function which will finish/undef > the handler it uses. > > ----------------------------------- > Frans Postma, (050-58) 81 852 > ATOS Origin, Unix Support > "If at first you don't succeed, skydiving isn't for you" > > > -----Oorspronkelijk bericht----- > > Van: Ian Harisay [mailto:[EMAIL PROTECTED]] > > Verzonden: maandag 10 februari 2003 18:35 > > Aan: [EMAIL PROTECTED] > > CC: [EMAIL PROTECTED] > > Onderwerp: Re: DBD:Oracle holding on to cursors ? > > > > > > Are you explicitly closing your statement handles and explicitly > > committing? If you are running endlessly and not committing it will > > lock your tables until that commit happens. There is more to it than > > just this. The other things to cover are on the DBA side. > > > > [EMAIL PROTECTED] wrote: > > >Hi, > > > > > >I have a script which runs in non-stop in the background on > > > > a HPUX system. > > > > >It checks various table in my database and updates some rows > > > > when needed. It > > > > >connects to the database (Oracle 8.1.7.2.0) once at startup > > > > and then execute > > > > >a never ending loop with the update statements, sleep(15) and loop.. > > > > > >So far so good. However, we've noticed that somehow tables > > > > remain locked due > > > > >to this script running non-stop. We did NOT have this > > > > problem when the > > > > >script was run every minute from a crontab. Thats not > > > > desirable due to slow > > > > >connects. > > > > > >Does anyone have experience with using DBD:Oracle in a never-exiting > > >process/script ? It appears to be a cleanup problem of some > > > > sort but I'm at > > > > >a loss as to WHERE the problem is located. I finish and undef every > > >statement-handler I use, most of which are "my" into a > > > > sub-routine and thus > > > > >destroy on returns anyway. > > > > > >Locks and problems in the database DO dissappear when we > > > > stop and re-start > > > > >the script so it's definitly related to it. > > > > > >Suggestions ??? > > > > > >----------------------------------- > > >Frans Postma, (050-58) 81 852 > > >ATOS Origin, Unix Support > > > "If at first you don't succeed, skydiving isn't for you"
