Cursor/Commit/Lock/Release

From: <gjay_at_erols.com>
Date: Sat, 25 Jul 1998 12:14:00 -0400
Message-ID: <35BA0447.8C92287A_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.ca_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 Sat Jul 25 1998 - 18:14:00 CEST