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.

less commits...or bigger rollback segments...or multiple passes through the data...

HTH

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."