Re: Rollback Segment

From: <asprusch_at_my-dejanews.com>
Date: Mon, 27 Jul 1998 05:55:33 GMT
Message-ID: <6ph4ok$bg2$1@nnrp1.dejanews.com>

You forget the where clause in your update statement. You have two options. First you can declare the cursor as for update. But then, you can't commit in the loop. Second you feth the rowid in the cursor. Then you can update the record by selecting it via rowid. This method have the advantage, that you can commit in the loop. The code is shown below:

   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 rowid, 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)
!!!!!                where rowid = cur_st_rec.rowid;
   26
   26             fetch_count  := fetch_count + 1;
   27             update_count := update_count + 1;
   28             commit_count := commit_count + 1;
!!!!!             if commit_count > ... then commit; end if;
!!!!!             -- This only works with the rowid method
!!!!!             -- because a commit in a loop with a for update cursor
!!!!!             -- will release the cursor's locks

   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 /

Hope this will help,
Andreas Prusch

In article <35BB5175.31A29B2F_at_erols.com>,   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 /
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 27 1998 - 00:55:33 CDT