ORA-01555 snapshot too old problem.

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. Received on Sun Nov 05 2000 - 20:32:54 CST