UNDOTBS Tablespace is Full what now?

Question

Bit of a newbie to oracle 10g. 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/rollsback?

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

Thanks in advance

Karl

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

Have a look at your undo retention period - make sure it isnt too large, otyherwise oracle will just keep buffering data until the tablespace fills up. An appropriate period will depend on your app

Answer 3

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.