Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Dropping Undo Tablespace

Dropping Undo Tablespace

From: Ora <ora_geek_at_yahoo.com>
Date: 15 Aug 2002 06:21:55 -0700
Message-ID: <fa5d6718.0208150521.612ff144@posting.google.com>


Oracle 9i: Rel 2

I had 2 undo Tablespaces undotbs1 and newundo - and undotbs1 was the one currently used.

I switched to newundo.

Did some DML and had some uncommitted transaction on it , when switched back to the old untotbs.

I wanted to drop the newundo Tablespace.

I could not do it due to : ORA-30013: undo tablespace 'NEWUNDO' is currently in use

Thinking that the earlier transactions when newundo was in use might be holding up , I committed those transaction..

I again tried dropping newundo Tablespace.

I could not drop it .Same error :ORA-30013: undo tablespace 'NEWUNDO' is currently in use

Thinking that unexpired status of the Transaction might be holding up , I set the undo_retention time too low and ensure that all the transactions on newundo were expired..

I again tried dropping newundo Tablespace.

I could not drop it .Same error :ORA-30013: undo tablespace 'NEWUNDO' is currently in use

This was perplexing.

But when I started a new DML transactions, which started using the current undotbs1 …
I was able to drop the newundo Tablespace .

I realized that oracle does not allow us to drop an undo tablespace unless it is sure that there is another undo tablespace , not only available but functioning as well ..

Hope this the default behavior of Oracle .. is to ensure us greater stability

Hope many of you would have noticed it ? .. Any Coments.

OrA Received on Thu Aug 15 2002 - 08:21:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US