If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

From what I read last few weeks, in oracle 10g, if we use automatic undo management and undo retention, oracle will automatically allocate and deallocate segments when required and we do not need to bother about it growing once we set a proper size. But later read that if we do not set the MAX_FILE_SIZE (i think this is what it is), it will keep growing and allocate segments all over the file system and occupy space.

these are my questions:
1. When undo TS is 15GB, does it mean that these 15GB is taken from the disk space and sitting here "useless".
2. Do I have to shrink it (to re-claim space)?
3. What are the steps to do it? I read in one form thread that I can create a new tablespace and change that to be the undo tablespace and drop the old one.
4. what other information about my DB or server should I provide to complete my question?

Can someone kindly explain the procedure? Reference to a documentation will also help. But if someone can list the steps, that will be really kind.

Thanks much

I thought I did a lot of online research but sure did miss this post. But I think, I have to do some more reading to get a real grasp of undo_retention and undo generation etc and how such things go together.

Undo tablespace would grow and grow if you want it. What restricts its growth is
- Undo_retention
- max_size of datafile
Now Oracle will shrink it when it comes under space pressure and when you retention time has been exceeded.
if it has space it would continue to grow ignoring retention time.