It occurred to me that it is sometime necessary to drop an UNDO tablespace after creating a new UNDO tablespace and changing our instance to use the new one. It occurred to me that Oracle should not allow you to drop and UNDO tablespace that has any active transactions against. If it did, you would not be able to rollback a transaction that had its undo information stored in a segment in the “old” tablespace. I tested this with the following test harness:
- create a new undo tablespace
- creating a table
- inserting a row into this table
- In another session alter the instance to use the new undo tablespace
- In this other session try to drop the “old” one
I found that when I ran the test above, an ORA-30013 was thrown when trying to drop the tablespace.
ORA-30013: undo tablespace ‘%s’ is currently in use
This is good news, as we know Oracle won’t allow us to create the condition I mentioned above.