UNDOTBS Tablespace is Full what now?

Question

I have been load testing an application and when I logged into the enterprise console I noticed that my UNDOTBS table space is full.

I know that the UNDOTBS table space is used to keep a copy of data that exists before a transaction starts. I thought that this data would be transient and expire once a transaction completes/rolls back?

Do I simply delete the table space, expand it, or is there a way of purging it?

UPDATE: The db has had no activity overnight. Can someone explain why oracle still needs this data?

Answer 1

Leave it. That's how it's supposed to be. Oracle will manage and re-use it as necessary.

Response to update:

THe UNDOTBS doesn't behave like other tablespaces... the data is left in there and marked as unused when it's no longer needed rather than being explicitly deleted.

You generally don't need to worry about it unless you're doing pretty sizeable DML and start getting errors, at which time you can look at increasing the UNDOTBS size, lowering your undo retention etc.

Answer 2

Besides what cagcowboy has already posted, if your undo is constantly full and you are getting errors. Make sure that your undo is size properly and that the undo retention is set at a value that is appropriate for the size. If the undo retention is set high, you'll need more space allocated to undo to retain information for the time limit specified in the undo retention. Only aged out transaction based on the undo retention will be cleaned and deleted.