How to Drop undo Tablespace

I create a new undo tablespace, then I want to drop the org undo ts and recreate is with a smaller size.

However, after switching Ii am not always able to offline the org tablespace.

I get an Ora-30042:Unable to offline the undo TS.

This error is always returned when using the procedure. This is in a test db, where I'm the only one logged in (sqlplus /nolog).

Lookin in de alert.log it states:
Undo tablespace 1 moved to Pending Switch-Out State.

What does this mean? How can I make sure the undo TS is not being used anymore, so I can offline it?

Using this command shift user UNDO and the try to drop old one.

ALTER SYSTEM SET UNDO_TABLESPACE = ....

Note of cautious:

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPACE drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo tablespace if undo information is needed by some existing queries

No comments:

topics