Commits across open cursors are not a good idea and I do not advocate them.
However, we live in an imperfect world and sometimes you just can't find a
big enough 2x4 to catch the developer's attention.
Off-lining the rbs that they are using may not be a certainty, and it may
be just a placebo, but I have found that it can allow a PL./SQL proc that
is doing commits across an open cursor to finish successfully.
Daniel Fink
<daniel.fink To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
@sun.com> cc:
Sent by: Subject: Re: convert RBS tablespace
from DMT to LMT (Oracle 8173) steps?
ml-errors
07/23/2003 03:24
PM
Please respond
to ORACLE-L
Oracle will not let you drop a rollback segment if there are active
transactions using it. However, it will allow you to offline the segment
and no new transactions can use it. I don't recall the exact status in
v$rollstat, but I think it may say pending offline.
As for the commit across cursors, the minute you commit, your transaction
has ended, even if the cursor is still open. When you issue the next
statement, you start a new transaction, which should assign you to a new
rbs. The one you were using is still offline. Of course, if you
online/assign/offline the rbs after each commit, you are reusing the rbs. I
have not tested this, so I may be wrong (Thoughts, Kirti?). However, you
may step on your own "free segment." Additionally, during the time
period of the online/assign/offline, there is a chance that another
transaction will be assigned to the rbs.
Daniel
Thomas Day wrote:
>
> How will you make sure that there are no active segments in your current
> rollback segments before you remove the datafiles with the OS? Even
after
> you take the rollback segments off-line, Oracle will continue to use them
> as long as they have an active segment.
>
> BTW --- One way to help to avoid the ORA-01555 when you are committing
> across an open cursor is to take the rollback segment that that cursor is
> using off-line. No one else will come in a step on the "free" segment
but
> your session will continue to process just fine until you close the
cursor.
> At least that's been my experience.
<< Attachment Removed : daniel.fink.vcf >>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
