How to reduce undo tablespace size in oracle?

Undo table space size is 30 GB, even if actions are not performed in the database.

+3
source share
1 answer

As the documentation says , we are pretty limited when it comes to UNDO tablespaces: there is no syntax for shrinking UNDO tablespaces, even in 11g. Without interference, the UNDO table space must be sized to fit our largest transaction. This means that if we have a huuuge batch process that runs once a year, then the UNDO table space should be large enough for it.

Why doesn't Oracle provide tools to reduce the UNDO table space? Because if we had transactions to stretch them to 30 GB, as soon as we get this load again. Freeing up disk space will not help us, because the UNDO table space will try to return it. If we used this space for some other purpose, our huge annual deal would fall.

Now, if you think that you had abnormal data processing that distorted your table space, and you are sure that you will never need so much UNDO, and you really need disk space, then you can use ALTER DATABASE syntax to compress individual files data.

+4
source

Source: https://habr.com/ru/post/1725386/


All Articles