Re: Commit in an open cursor

A copy of this was sent to markp7832_at_my-deja.com (if that email address didn't require changing) On Thu, 11 Nov 1999 14:52:36 GMT, you wrote:

>In article <ZSqW3.1299$j%2.146_at_cabot.ops.attcanada.net>,
> "Hisham Douba" <hdouba_at_netscape.net> wrote:
>> I appreciate your response on this. However, somebody mentioned that
>> rollback segments don't get released if a commit is inside an open
>cursor.
>> Do rollback segments stay reserved till the end of the loop or do
>they get
>> released when I commit?
>>
>As far as I know they are released on commit. I would take this
>question to Oracle support and post if they are not released in this
>case. We have a lot of processes that operate as described in this
>thread and we have not noticed problems so I suspect that the rbs data
>is released, but stranger things have happened.
>

definitely released. one of the sure ways to get an ORA-1555 (snapshot too old) is to commit in a cursor for loop that modifies the tables you are reading. You end up overwriting the rollback you previously generated but still need to provide the read consistent view in the outer query that is running across commits.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 11 1999 - 12:08:05 CST