Re: Locking Question (nowait, UPDATE etc)

Thank you for your help. We are working in this direction, but there are some problems: the select can't just restart with the same data until it finished, so we have to introduce some flag or time-stamp field into the table in order to keep track of what already was exported before 01555 happened and restart where it failed. This requires updating this flag for all records being exported and our DBA isn't happy - export process was supposed to be non-intrusive.
Q:
The question is - what is the most efficient way to mark the record where 01555 happened in order to continue from it without flagging each record?

If "select" is without "ORDER BY" clause, could the ROWID be used? Something like "where ROWID > :saved_rowid", but I don't think ROWID could be used in such a way... Should I use the value of the index or other unique key for this?

Thanks again,
Yuri.

ctcgag_at_hotmail.com wrote in message news:<20030404103941.543$ce_at_newsreader.com>...
> 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
Received on Fri Apr 04 2003 - 13:30:27 CST