understanding rollback segments

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. Received on Wed Apr 23 1997 - 00:00:00 CDT