Rollback Segment

From: <gjay_at_erols.com>
Date: Sun, 26 Jul 1998 11:55:34 -0400
Message-ID: <35BB5175.31A29B2F@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 - 10:55:34 CDT