Re: 01555 and select statement

On 10 May 2006 10:04:44 -0700, niy38_at_hotmail.com wrote:

>I had ora-01555 error with a stored procedure. The code is like this:
>
>for rec in (select * from a_big_table where some condition)
>loop
> update a_big_table
> commit
>end loop
>
>
>a_big_table has more than 1 million records, the whole process
>takes more than 10 days to finish.
>
>Now the weird thing is: after some time, it says:
>"PL/SQL procedure successfully completed.", but actually it didn't
>finish
>all update meeting the select statement condition, and in
>the alert.log, there is ora-01555 error with that select statement.
>
>if I run that again, after some time, same thing happen.
>
>questions:
>1) where Oracle save the value for "rec", because a_big_table has
> more than 1 million record and I think "rec" need a lot space to
>save,
> in memory or disk?
>
>2) my rollback tablespace just have less than 30 percentage usage,
> by the way, database is 9.2.0.5 and use undo.
>
> how to improve this code or how to tune my database?

1 Oracle puts the before image in the undo segments (9i) or rollback segments (8i and before).
However, as soon as you commit, the before image is a candidate to be overwritten.

2)
If you would have had a commit OUTSIDE the loop, the undo segment would have grown, thus causing ora-1555 to occur later in the future Do NOT commit inside a loop. This is *CAUSING* the ora-1555 error as it will release the before image, and cause the before image to be overwritten. To avoid ora-1555 do not commit inside a loop and increase init.ora undo_retention_time

--
Sybrand Bakker, Senior Oracle DBA