Re: Rollback extents

"nilanjan" <nilanjan_sarkar_at_hotmail.com> wrote in message news:6c8b1f5.0206271002.6a7b713e_at_posting.google.com...
> So...
> this still does not explain why the INSERT INTO..SELECT would generate
> a
> "ORA-01562: failed to extend rollback segment number 5 ORA-01628: max
> #
> extents (500) reached for rollback segment RBS04 "
> message !
>

Yes it does. Inserts generate rollback. In this case, they generated enough rollback to max-out the segment.

> As I understand (per above postings ) that
> 1.. SELECT would not generate rollback but would need to use it ( for
> any rows in the source table if changed )

Correct, but that wouldn't give rise to a 'maxextents reached' message.

> 2.. INSERT would need minimal (rowid + overhead per row) of Rollback.

Incorrect. Incorrect in the sense that 'minimal' means different things to different people. An insert's rollback is pretty small compared with a delete, that's for sure. But it's not nothing, and not even close to being practically nothing.

> The rollback segment could extend upto 500*320K = 160MB ; process
> inserted ~200000 rows and got the error ; 160000000/200000=800 bytes
> .
> Is that being required per row for insert into a new table ??

Could well be. Perhaps another transaction was using some of the segment and was generating rollback of its own. Perhaps there was an uncomitted transaction somewhere, preventing re-use of some of the segment's extents. Hard to know, really.

Regards
HJR Received on Thu Jun 27 2002 - 14:32:47 CDT