Re: What's in a Rollback Segment?

"Jeremy Russell" <jeremy.russell-NOTMYREALADDRESS_at_usa.net> wrote in message news:3d391d91.57696583_at_usenet.plus.net...
> Aha, figured you'd be the first responder - love that timezone
> difference.
>
> So, to paraphrase for my own understanding:
>
> - Oracle only writes the old data for the single column I updated
> - This data still writes an entire block to the RBS
> - That block is reserved thereafter for that transaction until it
> commits or rolls back.

That about sums it up. Mr Foote has pointed out a subtlety I didn't want to get into, which is that transactions can share the same rollback block from (I think) 8.1.6 onwards.

>
> A follow-up - if that transaction then updates another row, does it
> write rollback info to the currently used block (until that block is
> full) or does each DML statement in the same transaction acquire a
> further block?
>

Nope, the rollback block belongs to the transaction, so the transaction can use the same block, whatever it happens to update (recall that a transaction begins with a piece of DML and only ends with a commit or a rollback, so 'update emp...., update dept...., update something_else.... commit' is all one transaction, and all would share the same rollback block (provided they all fit, of course, as you say)).

Regards
HJR
> Jeremy
>
> On Sat, 20 Jul 2002 17:38:17 +1000, "Howard J. Rogers"
> <howardjr2000_at_yahoo.com.au> wrote:
>
> >It's just the column of the row being updated. Your few-byte transaction
is
> >thus going to occupy an entire 8192 bytes of a rollback segment (ie, a
> >transaction always occupies an entire rollback block, however small it is
in
> >itself).
> >
> >Regards
> >HJR
> >
> >
> >"Jeremy Russell" <jeremy.russell-NOTMYREALADDRESS_at_usa.net> wrote in
message
> >news:3d39010b.50393862_at_usenet.plus.net...
> >> OK, maybe it's too early in the morning, and maybe I'm just dumb, but
> >> what - in detail - is written to a rollback segment in Oracle 8i? I
> >> understand that a rollback segment records undo info for a transaction
> >> (used Oracle long enough for that level of comprehension at least!),
> >> but it was my understanding that the entire block would be written out
> >> for potential read consistency, recovery, rollback purposes.
> >>
> >> However, I have a feeling that may not be true.
> >>
> >> If I issue
> >>
> >> UPDATE emp SET ename='RUSSELL' where empno=7369;
> >>
> >> does the entire block where this employee resides get written, is it
> >> the entire row or is it just the changed column?
> >>
> >> If there's a link somewhere (technet or otherwise) that explains this,
> >> please let me have that, rather than taking up newsgroup bandwidth.
> >>
> >> TIA
> >>
> >> Jeremy Russell
> >
> >
>
Received on Sat Jul 20 2002 - 04:06:52 CDT