my undo tablespace is regularly increasing

[beksalur]

we are using local extent management and our undo tablespace is continuously increasing despite i commit.Even i restarting the db i see that our und tablespace is 90% full.

how can i empty the undo tablespace?

Thanks in advance.

 

[SantaMufasa]

Bek,

The reason your rollback-segment tablespace continues to use more space is probably because the rbs do not get smaller once you are done with them. If you choose to make your rollback segments smaller after they have become large following long-running transactions, you may specify the following:

ALTER ROLLBACK SEGMENT rbs_name STORAGE(OPTIMAL nnnM);

...where "nnn" is some number of megabytes.

This causes your rollback segment to shrink down to "nnnM" following a transaction that causes it to grow beyond "nnnM".

Dave
Sandy, Utah, USA @ 17:11 GMT, 10:11 Mountain Time

 

[karluk]

I don't think you can do much about the size of the undo tablespace unless you reduce the value of undo_retention. Otherwise Oracle will basically grab as much space as it needs from the undo tablespace to maintain the history that it needs. Just doing commits won't do any good. Oracle still needs to know the state of the database for the entire undo retention period.