Re: Cursor/Commit/Lock/Release

From: <gjay_at_erols.com>
Date: Sat, 25 Jul 1998 12:25:57 -0400
Message-ID: <35BA0715.32612E80_at_erols.com>

Sorry about a small typo. Instead of s_id in my previous message, I typed as ca_s_id. Please read it as s_id.

gjay_at_erols.com wrote:

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 Sat Jul 25 1998 - 18:25:57 CEST