Re: ORA-01555 snapshot too old problem.

<saeedt7777_at_my-deja.com> wrote in message news:8u558n$93m$1_at_nnrp1.deja.com...
> 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.

Insert rubbish into dummy table, and do so such that every single rollback segment is affected. Then perform your enormous transaction... the presence of the dummy blocking transaction will gurantee protection of the rollback required for your transaction. When your real transaction has completed, do a rollback of the dummy transaction.

Scripts to automate the entire process are available at www.ixora.com.au.

Regards
HJR
>
> 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 Mon Nov 06 2000 - 01:10:32 CST