Re: 01555 and select statement

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

What kind of where clause is on this update?

> commit
> end loop
>
> a_big_table has more than 1 million records, the whole process
> takes more than 10 days to finish.

How much more than 1 million? It takes 10 days update (something more than) 1 million records? (or are you updating 1 million records one million times?

>
> 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.

I don't know if that is weird or not. What kind of exception handing do you do?

>
> 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?

It doesn't run the entire query at once and save the results anywhere (if it did, you wouldn't be having the 1555s). It executes just enough to get a few rows at a time, and it saves the execution pointers so it can pick up execution where it left off when it needs more rows.

>
> 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?

You tune your database by finding out what is taking so friggin long and fixing it.

How to improve the code depends on what you are trying to do. If the entire set of updates is one logical transaction, then run it as one logical transaction (which, with tuning, may be possible). If the updates are separate logical transactions, then why are they being driven off from one select statement? Are the updates idempotent? Are they self-documenting?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB