Re: ORA-01555 snapshot too old problem.

saeedt7777_at_my-deja.com wrote:

> I have a query that needs to update ALL the rows in a large table.
> I am getting snapshot too old problem ORA-01555 error.
> What is a good technique to update all the rows in a large table
> without getting this error? Let's say I do not want to increase
> the rollback segments. Also the trick of opening and closing the
> cursor in my case will not work because I have to update all the rows
> in the table. Even if I delay the commit and do it every 5000
> times there is no guarantee that I will not hit the
> problem again.
>
> The question bolis down to one question. What is the sure and the
> safe way to update all rows of a large table in Oracle ?
>
> Here is the statement :
>
> DECLARE
> CURSOR ids_cursor IS
> SELECT rowid, c_userId FROM dw_user_dim WHERE c_userId!=-1;
> BEGIN
> FOR v_id IN ids_cursor LOOP
> UPDATE dw_user_dim
> SET c_programName = (SELECT c_programName FROM t_user WHERE
> c_id=v_id.c_userId)
> WHERE rowid=v_id.rowid;
> COMMIT;
> END LOOP;
> END;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

The fundamental problem here is that the code is FETCH'ing across COMMIT's. Oracle allow but do NOT support this type of code.

Anyway, you should not commit more than (no. of online, non-system rollback segments) - 1. If your transaction needs large rollbacks, it needs large rollbacks.

Hugh Received on Sun Nov 19 2000 - 09:36:33 CST