Re: understanding rollback segments

From: Jim Yoshii <yoshii_at_planet.net>
Date: 1997/04/24
Message-ID: <335F9A97.7C9E@planet.net>#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.
>
> Many thanks for any help.
>
> Dave.

CREATE TABLE is a DDL statement, it cannot be rolled back, and it doesn't use rollback segments.

You should have done the obvious:

CREATE TABLE foo AS SELECT * FROM bar;

• Jim Yoshii

  ---

NOTICE TO BULK EMAILERS: Pursuant to US Code, Title 47, Chapter 5, Subchapter II, 227, any and all nonsolicited commercial e-mail sent to this address is subject to a download and archival fee in the amount of $500 US.

E-Mailing denotes acceptance of these terms.

---

Received on Thu Apr 24 1997 - 00:00:00 CDT