Update Commit Loop Issue

[dgmill]

Hi. Relative pl/sql newbie here. This update script with a commit loop is just about to drive me insane. I've lowered the interval to every 1000, but I'm still running out of roll back segments (ORA-01562: failed to extend rollback segment number 10
ORA-01628: max # extents (200) reached for rollback segment LG_RB3). I'm beginning to think adjustments need to be made on the server end, like increasing rollback segment size, but more than likely I've got a stupid error in my code. Anybody see what's wrong with it?


DECLARE
commit_interval NUMBER := 1000;
v_count NUMBER := 0;

CURSOR mile_cursor IS
Select begin_mp, length, end_mp
from pavement.pcs2002;

BEGIN
FOR rec_miles IN mile_cursor
LOOP
UPDATE pavement.pcs2002 SET length = 4;
v_count := v_count + 1;
IF v_count >= commit_interval THEN
COMMIT;
v_count := 0;
close mile_cursor;
open mile_cursor;
END IF;
END LOOP
COMMIT;
END;

 

[JediDan]

Try this:

begin
    for rec_miles IN mile_cursor
        UPDATE pavement.pcs2002
        SET length = 4;
        v_count := v_count + 1;
        
        if mod(v_count,1000) = 0 then
            commit;
        end if;
    end loop;
    commit;
end;

No guarantees!

 

[carp]

I think your problem MIGHT be you have no WHERE clause in your UPDATE statement. Consequently, every time you go through the cursor, you are updating every row in the table!If the table has a lot of rows, you are generating a lot of rollback data. You are also not accomplishing much with this - if your intent is to update every row to a value of 4, then you don't need a cursor.

 

[Sudhi78]

the problem is bcoz of the close and open in the loop.
after all why cant u directly say UPDATE statement, why the looop here? u r just updating a column to 4.

 

[user1972]

Try increasing the MAXEXTENTS. I think thay should work.
the problem is not in the code it is the database(server).

 

[sem]

I agree with Carp.

Regards, Dima

 

[carp]

The CLOSE/OPEN sequence within the cursor loop bothered me too - but JediDan took care of that with his posted code. However, that code was still updating the entire table with every iteration.

 

[JediDan]

I agree with Carp as well... even if the missing WHERE clause isn't the problem, the code still doesn't make (practical) sense without it.

 

[JimboNWUK]

It is DEFINITELY the lack of a where clause that is your problem.
Include ROWID in your select list and add WHERE ROWID=cursorname.ROWID in your update. In fact I would not even use a declared cursor here -- its's FAR easier to declare your statement at the top of the loop like so:

DECLARE
commit_interval NUMBER := 1000;
v_count NUMBER := 0;

BEGIN
FOR miles_rec IN (
Select begin_mp,
length,
end_mp,
ROWID
from pavement.pcs2002)
LOOP
UPDATE pavement.pcs2002
SET length = 4
WHERE ROWID = miles_rec.ROWID;

v_count := v_count + 1;
IF v_count >= commit_interval
THEN
COMMIT;
v_count := 0;
END IF;
END LOOP
COMMIT;
END;

The effect is the same as using SELECT...FOR UPDATE OF but I don't think it takes out any locks.

Regards,

Jim. (PL/SQL programmer since it 1st came out!)