Re: Unable to really switch undo tablespace

Thanks Tanel for what appears to be a most logical explanation of what is happening.

Tanel Põder wrote:
> Hi,
>
> I think the issue here is that some of the datablocks haven't been
> cleaned up after transactions in those have completed. So when you read
> such block, it has some rows in it with lock bytes set, ITLs pointing to
> old undo segments.
>
> When the undo segments still exist in data dictionary (despite their
> tablespace is online), Oracle wants to read their headers to see whether
> the transactions corresponding to uncleaned blocks are committed.
>
> The solutions be:
>
> 1) drop old undo tablespace - then Oracle knows that the transactions
> have finished (as you couldn't drop the undo segments otherwise)
> or
> 2) performn delayed block cleanout on all blocks which could be unclean,
> having references back to old undo tablespace. This should be done both
> for tables and indexes. You could either force a full table scan on all
> suspect tables and fast full index scan on all of their indexes - or you
> could do an analyze on all those segments (btw, make sure that you
> analyze 100% of these then, not just a sample). This should result in
> cleaning up all datablocks.
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l