Problem with undo tablespace and roll back segments
701667Mar 23 2010 — edited Mar 23 2010Hello!
I have been facing a problem with my undo tablespace. Actually the undo tablespace had been grown up to 15 GB while it was set to auto extend on. As there was no way to make it shrink. So I created a new undo tablespace and switch to it and after that I tried to drop the old one but unfortunately I was not able to drop it whenever I try to drop it, it throws an exception about the rollback segments. When I run the following query for old undo tablespace then it returns around more than 2500 entries with two status: "PARTLY AVAILABLE" and "OFFLINE". There are only 78 entries with OFFLINE status while the remaining entries with PARTLY AVAILABLE status exist.
select * from dba_rollback_segs where tablespace_name='UNDOTBS1';
When I query for pending transactions by the following sql script then it shows 2677 entries with STATE="prepared" and STATUS=P.
select * from sys.pending_trans$;
I am totally stuck here and no idea what can I drop the old undo tablespace.
Please help.
Arshad.