Jul 5, 2005

why does undo tablespace grow so fast?

-Check the datafiles of undo tablespace are autoextened or not. if they are set to auto extensible, it will not reuse the space for the new transaction. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.

The value for undo_retention also has a role in growth of undo tablespace. Ex: when load data into tables immediately after each other, then the UNDO_RETENTION time was probably too long, which resulted in more space being used instead of reused.

-UNDO is using autoallocation option of LMT. As the number of the extents goes up, the extent size will be increased too. When the number of extent reaches hundreds, the size could be 8M to 64M+ each. As it reaches thousands, the extent size will be even bigger.

This algorithm is meant to fit most cases, but if you know the size of most of your transactions you can use UNIFORM rather than AUTO.