Re: Unable to really switch undo tablespace

Hi,

If you have a big transaction which modifies lots of blocks before committing, then some of those blocks containing uncommitted data have to be flushed onto disk (by DBWR). When this transaction commits now, it makes no sense to read those blocks into cache again just for cleaning the locks. Oracle uses undo segments to find out whether the transaction has completed or not (as when transaction commits, just a status flag (and commit SCN) in appropriate undo segment slot is set.

Now when a query/transaction finds a locked row it has to check whether the transaction in corresponding transaction table slot has been marked ended (thus maybe even overwritten with a transaction with newer seq#) or not. If yes, we can perform delayed block cleanout, if no we will wait for corresponding TX lock. If this TX lock doesn't exist anymore then apparently instance has crashed and we can roll back the required block on our own (as from 8i we can open the database before all transactions are rolled back and an interested process can roll back required blocks on its own, leaving rest to SMON or its parallel slaves).

So this stuff has to persist (and persists) across instance bounce.

Tanel.

• Original Message ----- From: "Alex Gorbachev" To: Cc: ; ; "ORACLE-L"

Sent: Sunday, December 25, 2005 6:05 PM
Subject: Re: Unable to really switch undo tablespace

> The author mentioned that he did try it after database bounce.
> Perhaps, I am missing something about delayed block cleanout, but that
> would be valid explanation only when instance hasn't been bounced.
> Please correct me if I'm wrong.
>
> 2005/12/21, Wolfgang Breitling :
>> Thanks Tanel for what appears to be a most logical explanation of what
>> is happening.
>>

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