01555 and select statement

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? Received on Wed May 10 2006 - 12:04:44 CDT