Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: understanding rollback segments

Re: understanding rollback segments

From: Kelly Young <young_at_maricopa.edu>
Date: 1997/04/23
Message-ID: <335E4234.3EF8@maricopa.edu>#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.

I've only had one cup of coffee this morning. Normally I don't like to think about rollback segments until I've had my second cup; but here goes anyway. Your rollback tablespace is probably not big enough. When you do a commit, Oracle assigns you another rollback segment for your next transaction. So you are continually going from one rollback segment to another. And as you do this, each rollback segment is using a certain amount of storage in the rollback tablespace. The total space used by all your rollback segements is more space than you have allocated to your tablespace. As I said before, I'm not responsible for anything I say until after the 2nd cup of coffee. Received on Wed Apr 23 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US