Re: create index and rollback segs?

Jonathan Lewis wrote at 08-Dez-99 20:44:42 on the subject Re: create index and rollback segs?:

>I'm still thinking about the massive use
>of rollback, which shouldn't happen.

>Bear in mind though that 'create index'
>starts with an implicit commit, which immediately
>undoes the effect of your 'set transaction'.

Really? I always thought 'create index' *ends* with a commit. Anyway creating an index does not have anything to rollback so there should not be any use of rollback segments.

>Is this problem totally repeatable ?
>Do you have the TEMP segment set up as
>a temporary segment - should be irrelevant,
>but you never can tell.

Well, it's *quite* repeatable. That is one index of three was created most likely because the transaction got the big rollback segment.

We now followed the advice of Oracle Support to take all rbs except the big one and system offline. Then the create index succeeded. Maybe when there is some spare time, I will rerun the create index with sql_trace enabled to see what recursive SQL produces that much rollback. But for now, I got my indexes and am happy with them. :-)

Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |