On 07/19/2011 09:47 AM, Jan-Hendrik Boll wrote:
Hi, I have one question about what happens after the ORA-01555:If i am getting it correctly: the information stored in the block-header says: uncommited changes, oracle tries to read the information from undo which leads us to ORA-01555.And what happens next to our changed block?Is oracle perfoming a rollback on the uncommited blocks? (Wouldn't that lead to ORA-01555 too?)Regards Jan-Hendrik Boll
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>