Re: understanding rollback segments

From: Tim Witort <trw__at__medicalert.org>
Date: 1997/04/23
Message-ID: <335EA149.B0E@_medicalert.org>#1/1

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.

Does it say "unable to allocate extent of size...." or "maximum number of extents reached for rollback segment..."?

Anyway... short of switching rollbacks to huge ones then using SET TRANSACTION USE ROLLBACK SEGMENT biggie, you could do the following if the table you are copying has some monotonically increasing numeric key:

declare

	rangeStart	NUMBER;
	rangeStop	NUMBER;
	rangeIncr	NUMBER;
	rangeMax	NUMBER;
begin
	rangeMax := 27000;
	rangeIncr := 1000;

	rangeStart := 0;
	rangeStop := rangeIncr;

	LOOP
		insert into mytable
			select
				whatever
			from
				myothertable
			where
				myID between rangeStart and rangeStop;

		commit;

		rangeStart := rangeStop+1;
		rangeStop := rangeStop+rangeIncr;

		EXIT WHEN rangeStart > rangeMax;
	END LOOP;
	commit;

end;
..
/

• TRW -- |||| remove the underscores in my address to reply |||| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trw_@_medicalert.org || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Received on Wed Apr 23 1997 - 00:00:00 CDT