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