Re: understanding rollback segments

Dave Wotton wrote:

> hi all,
>
> I thought I understood how rollback segments worked, but now I'm not
>
> sure. Can someone explain what is happening here...
>
> I simply want to copy a table ...
>
> The "obvious":
>
> CREATE TABLE foo AS SELECT * FROM bar;
>
> Didn't seem right. If foo is big, I thought the rollback segments
> would blow if I tried it.
>
> Intead I tried this PL/SQL ...
>
> declare
> cursor c1 is
> select ....
> from bar;
>
> c_rec c1%ROWTYPE;
>
> ctr number ;
>
> begin
>
> open c1;
>
> loop
>
> fetch c1 into c_rec;
> exit when c1%notfound;
>
> ctr := ctr + 1;
>
> insert into foo
> values ( c_rec.xxxx, c_rec.yyyy, c_rec.zzzz );
>
> if ctr > 1000 then
> commit;
> ctr := 0;
> end if;
>
> end loop;
>
> close c1;
>
> commit;
> end;
>
> ie. I am trying to copy the table by inserting rows into the new
> table 1000 at a time, and commiting.
>
> But I still get an error "ROLLBACK SEGMENT cannot be extended".
>
> I thought that, by doing regular commits, the rollback segment
> would reuse the extents in the segment, rather than continue
> to allocate more extents. Why is this happening? No-one else
> was using the database when I was doing this.
>
> I have found another solution using exp/imp, but I'd really like
> to know what it is I misunderstand about the above approach.
>
> Many thanks for any help.
>
> Dave.

 Just to add to the good replies you've already got from other posters, try 'watching' your rollback segments as you are running your transactions via Server Manager or SQL*DBA. This should be quite easy as you had no other transactions running on the system.

Apart from what has already been posted - What was the exact error message?; Get a big rollback segment via USE ROLLBACK SEGMENT, you might also want to look at your existing rollbacks - have they got an OPTIMAL setting? Do the require manually shrinking? Do you have enough and of the right size? Etc. Etc.

Steve Phelan. Received on Thu Apr 24 1997 - 00:00:00 CDT