Re: Locking Question (nowait, UPDATE etc)

gu-news_at_earthlink.net (Yuri) wrote:
> I have a situation very similar to the original poster:
> Large query constantly exporting data from the table while
> the data is updated by multiple other sessions. The difference
> is that the exporting query doesn't need to lock the records it
> is exporting. When the SELECT cursor, doing the export, gets to
> the end of records it closes and reopens again, so if it missed
> some of the records that were updated, it will get them on the
> next SELECT - the fact that some records it selects will be out
> of date is not a concern here.
>
> What worries me here is that the query for the exporting cursor
> selects large number of records and stays open for some time,
> which is causing the "ORA-01555: snapshot too old" error.

Catch the 1555 and restart the procedure. Since you don't mind if some of the recrods are out of date, then as long as you occasionally make it to the end without a 1555, you have no problems.

Or increase your undo retention.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB