Hi,
The solutions be:
Tanel.
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 -- //www.freelists.org/webpage/oracle-l
-- Regards
Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l