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
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l


Other related posts: