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 -> Re: Dropping Undo Tablespace

Re: Dropping Undo Tablespace

From: Richard Foote <Richard.Foote_at_bigpond.com>
Date: Fri, 16 Aug 2002 10:50:20 +1000
Message-ID: <3D5C4C4C.6B7978F9@bigpond.com>


Hi Ora,

I hate to do this to you again but no, this isn't the behavior of the undo tablespace when you wish it dropped.

Check out the following:

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
undo_management                      string      AUTO
undo_retention                       integer     0
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2


In my one and only other session, I have a transaction that made a change and committed. There is no other activity in the DB. In this session, I now perform the following.

SQL> alter system set undo_tablespace = undotbs;

System altered.

SQL> drop tablespace undotbs2;

Tablespace dropped.

Worked immediately without any further activity in the database.

Maybe the undo retention period has some impact (although I don't think so as there are clear documented warnings about snapshot too olds and the such it you drop undo tablespaces in this manner). Anyway, I tried again.

SQL> create undo tablespace undotbs2 datafile size 10m;

Tablespace created.

SQL> alter system set undo_retention = 6000;

System altered.

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
undo_management                      string      AUTO
undo_retention                       integer     6000
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

I then did some more activity in another session and committed. Nothing else is happening in the system when I re-attempted to drop tablespace.

SQL> alter system set undo_tablespace = undotbs;

System altered.

SQL> drop tablespace undotbs2;

Tablespace dropped.

This is on 9.0.1.1.1 on Windows 2000.

Then performed the same on my new 9.2.0.1.0 DB on Windows XP with same results.  

So in summary, I was unable to reproduce the problem.

The documentation simply says you can't drop an undo tablespace that's needed to rollback uncommitted transactions. Fair enough.

Now that's not to say the issue didn't arise on your installation, or can't arise on other installations/environments with some other set of variables, but it's incorrect to suggest that the behavior you described is necessarily the standard behavior.

Cheers

Richard

Ora wrote:
>
> 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 - 19:50:20 CDT

Original text of this message

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