Re: understanding rollback segments

In article <5jkuhe$o9g_at_dns.camcnty.gov.uk>,   Dave Wotton <Dave.Wotton_at_it.camcnty.gov.uk> 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.

If you are using Oracle 7.2 or above, than you can use

CREATE TABLE FOO AS SELECT * FROM BAR UNRECOVERABLE. This will avoid any potential rollback seg problems and not generate any redo log entries. It will also run much quicker.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet