Help! commit not working

[mb]

I have a large table (23 million records) that im trying to update using a plsql block. This commits after updating every record in the block.

FOR equityprice_rec in equityprice_cur
LOOP
newDate := equityprice_rec.tradingdate +
daysDifference;

UPDATE equityprice
SET tradingdate = newDate
WHERE current of equityprice_cur;
commit;
END LOOP;

But still I keep getting an error message like the following:

ERROR at line 1:
ORA-01562: failed to extend rollback segment number 12
ORA-01628: max # extents (70) reached for rollback segment R11
ORA-06512: at "GIRS.UPDATE_EQUITY_DATE", line 4
ORA-06512: at "GIRS.UPDATE_EQUITY_DATE", line 20
ORA-06512: at line 1

Does anyone know why the commit isnt working?

[santoshym]

Hi

Try to commit after every 1000 records

Regards

[rlowe]

Or you can:

1) Increase disk space
2) Set your rollback space to expand indefinately

R

[dsuva]

Hi !

it's got nothing to do with your commit statement . Create a large rollback segment eith maxextents set to a very high number or to unlimited. And then set the transaction to that rollback segment.

The problem is that your rollback segment is simply runnng out of space.

Suva

[mb]

I thought that commiting after every record should cause only one transaction to be stored in the rollback segment. AFter commiting I cannot rollback anyway.

[shawish_sababa]

Hi

first, how many lines o you need to update? if, for example, we are tolking about 1k lines that every line is 1k in average, then you should created be chngeing the size of your rollback segments. befor you do that. read from this site about the issue.
if, on the other end, we have 1000k lines then you can: 1) use dsuva proposal. 2) use santoshym proposal. i would personaly use commit every x lines.

[helpme]

I had faced with same kinda problem

What I did was...

This was the seq of steps i did in panic!!!!

connect as sys user and kill all on going sessions

commit;

alter tablespace RBS default storage (pctincrease 1);
alter tablespace RBS default storage (pctincrease 0);
alter tablespace RBS coalesce;

I dunno if it is rite...
But It dint screw up my database

[jmodic]

Whatever have been proposed here in this thread does not answer the original question. All proposed solutions missed one cruicial point: transaction is updating a single column (tradingdate) of a *single record* (WHERE CURRENT OF equityprice_cur), after which a commit is isued. So transaction is realy extremely short one and it *should not* cause any ORA-1628!!! Not even in theory! The sizing of rollback segment has nothing to do with what this PL/SQL block *should* be doing.

But obviously something is hidden here, something must be missing from the whole picture.

mb, is there any trigger on that table that is cusing mass changes to some table(s) whenever an update is executed on EQUITYPRICE table? That is the only thing that I could think of that might be causing your error.

[mb]

There are no triggers, but the table has 23 million records in it. Someone has suggested the problem may be because I use:

cursor equityprice_cur is
SELECT tradingdate
FROM equityprice
FOR UPDATE;

Oracle may be storing all of the entries of trading date in the rollback segments, which may mean that the rollback segments need to HUGE.

If this is true does anyone know of a work around?

[shawish_sababa]

how many times this loop reapets itself?