Re: Unable to really switch undo tablespace

I had the same problem some time ago. The solution, and IIRC I found it even on Metalink, was to offline and then drop the old undo tablespace. I know, it sounds a bit unnerving. I tried the same as you - offline the tablespace for a while, THEN drop it. But as I said, I ran into the same problem that some transactions attempted to use the offlined OLD undo tablespace - even after a restart. In order to feel better about it I dropped the old undo tablespace immediately after a startup where I could be reasonably sure that no transactions would be active yet.

Giovanni Cuccu wrote: 
Hi all,
     I'm facing a problem with undo tablespace with oracle 9.2.0.4
running on linux redhat 3.
Due to an error a procedure  has consumed a lot of undo space, causing
the growth of the undo tablespace datafile up to 16gb (the datafile
was in autoextend mode). Now I'd like to go back to the previous
situation where the undo tablespace was 4gb.
I created a new undo, namely undotbs_02, and made the switch as
described in the manual via
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
the switch completed but when I tried (some weeks later and after a
clean shutodown) to offline the orginal one some of the programs began
to report
"unable to read datafile 2..."
Needless to say datafile 2 is the old undo tablespace.
If I query the dba_rollback_segs I can see all the rollback segments
belonging to the old undo tablespace as offline, the dba_undo_extents
show the status as unexpired.
I already shutdown immediate the database, after the switch and server
days later, is there anything I can do to get out of this enpasse?
I already opened a tar (now SR) but until now I didn't received any
useful suggestion.
Thanks to all,
     Giovanni




--
--------------------------------------------------------------------
Another free oracle resource profiler
http://sourceforge.net/projects/oraresprof/
Now version 0.9
--
http://www.freelists.org/webpage/oracle-l




--
Regards

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


Other related posts: