How to reduce undo tablespace size in oracle?

Question

Undo tablespace size is 30 GB even not activities going on DATABASE.

Answer 1

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

Why don't Oracle provide tools for shrinking the UNDO tablespace? Because if we have had the transactions to stretch it to 30GB once we are likely to have that load again. Freeing up the disk space won't help us, because the UNDO tablespace is going to try to reclaim it. If we have used that space for some other purpose then our huge annual transaction will fall over.

Now, if you think you have had soem abnormal data processing which has distorted your tablespace and you are convinced you're never going to need that much UNDO ever again and you really need the disk space then you can use the ALTER DATABASE syntax to shrink the individual data files.