Re: Fwd: Snapshot too old from READ-ONLY table (data pump export)

ORA-01555 isn't a problem due to uncommitted data. It's linked to committed data. What happens is that as long as a change is uncommitted, then the previous value is kept in rollback segments (undo) - as long as you don't run out of undo space, everything is fine.   Trouble starts when a change is committed, because then Oracle doesn't feel compelled to keep previous values. It will try to keep them as long as possible, but not forever. When a change is committed, you can no longer rollback; and from then on previous values can only be needed for giving a consistent view to a concurrent query that would have started before the transaction was committed (and therefore at a time when it couldn't see the change) and that must be kept in a blissful ignorance of the change until it has returned its very last row (this is the basic principle of read-consistency in Oracle - whatever the duration of a query, it must "see", as long as it runs, the database in the very same state as it could see it when it started running). I am ignoring flashback queries here, but you could extend the reasoning. It's a long query that runs while many, many changes occur AND ARE COMMITTED that has a problem and gets ORA-01555, because if a large number of changes requires massive amounts of undo, Oracle will happily reuse undo space of transactions that have been committed, even if a SELECT needs the data for consistency. The SELECT will abort, and hopefully if you run it again , if it had the mishap of running during a burst of changes to the database, it will complete successfully. If not, review your code.

HTH,

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


--
http://www.freelists.org/webpage/oracle-l