Re: Rollback Segment

Increase the size of your rollback segment. It doesn't have space to expand to another extent.

Try to make a huge rollback segment for this transaction only.

use

set transaction use rollback segment <rb name>;

Hope it can help

Winnie

gjay_at_erols.com wrote in message <35BB5175.31A29B2F_at_erols.com>...
>I want to update a column in a table with 3 million rows. Since it has
> to update 3 million rows, in order to avoid any problems like time out
>
> and ailed to extend rollback segment, problems, I wanted to commit
>every
> 1000 records.
>
> I wrote a small PL/SQL script and tested on a test database where
>there
> are only 500 records.
>
> When I ran the update
> Without Cursor:
> update xyz x
> set x.s_tp =
> (select s.s_tp
> from st_tp_tbl s
> where s.s_id = x.s_id)
> where x.date_time > sysdate - 4;
>
> it successully completed in less than a fraction of a second. The same
>
> update by using cursor failed, giving the rollback segment problem.
> Here is the error message.
>
> DECLARE
> *
> ERROR at line 1:
> ORA-01562: failed to extend rollback segment (id = 7)
> ORA-01628: max # extents (121) reached for rollback segment R07
> ORA-06512: at line 21
>
> Can any body explain the problem and provide solution. I appreciate.
>
> PL/SQL script usign cursor and counts.
>
> SQL> DECLARE
> 2
> 2 fetch_count number (9);
> 3 update_count number(9);
> 4 commit_count number(9);
> 4
> 5 CURSOR cur_st IS select s_id from xyz
> 6 where date_time > sysdate - 4;
> 6
> 7
> 7 cur_st_rec cur_st%ROWTYPE;
> 8
> 8 BEGIN
> 9
> 9 fetch_count := 0;
> 10 update_count := 0;
> 11 commit_count := 0;
> 12
> 12 OPEN cur_st;
> 13 LOOP
> 14 FETCH cur_st INTO cur_st_rec;
> 15 dbms_output.put_line (cur_st_rec.s_id);
> 16 EXIT WHEN cur_st%NOTFOUND;
> 17 if commit_count >= 100 then
> 18 commit;
> 19 commit_count := 0;
> 20 end if;
> 21 update xyz x
> 22 set x.s_tp =
> 23 (select s.s_tp
> 24 from st_tp_tbl s
> 25 where s.s_id = cur_st_rec.s_id);
> 26
> 26 fetch_count := fetch_count + 1;
> 27 update_count := update_count + 1;
> 28 commit_count := commit_count + 1;
> 29
> 29 END LOOP;
> 30 COMMIT;
> 31 close cur_st;
> 32
> 32 dbms_output.put_line ('UPST.SQL successfully
> completed');
> 33 dbms_output.put_line('TTOTALS: fetch count = ' ||
> to_char(fetch_
> count)
> 34 || ' update count = ' || to_char(update_count)
> 35 || ' commit count = ' || to_char(commit_count));
> 36
> 36 END;
> 37 /
>
>
>
Received on Sun Jul 26 1998 - 18:39:26 CDT