How to shrink my undo tablespace

[mayse]

Could someone please tell me how to shrink my undo tablespace back to its original size or to some manageable value. It currently sits at 1.3 Gb.
It grew to that size when I intentionally inserted 9,000,000 rows into a table with a commit at each insert.
Since then I have truncated the table but the undo still remains at the same size.

Oracle Version 9.0.1 on NT 4

Any help will be greatly appreciated.

Thanks
May$e

[Raminder]

Do you have an 'optimal' clause in your undo tablespace?
I have never used 9i but in 8i, you simply shrink the tablespace by

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;


This statement attempts to reduce the size of the rollback segment to the specified size, but will stop short if an extent cannot be deallocated because it is active.

[mayse]

Thanks Raminder.

I don't know if I can srink the undo tablespace in 9i.

By the time I got your response, I had created another undo tablespace took the original undo t/s offline and made the new one on line and bounced the d/b.
It worked well and I was able to delete the undo tablespace and its file.

But it would be interesting to findout if this is a bug or if I had other options than the one I decided to take.

Thanks
May$e

[gpsingh]

1) You can shrink the roll back segments as

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

Then u can shrink the tablespace by shrinking the datafiles as before, only thing
u should care about is the undo_retention period, the tablespace should be
sized keeping in mind the undo_retention period set.

[mayse]

Hi GPSingh,

I am aware of that command for the 8i env, but I don't think it applies in this situation. I am dealing with the undo tablespace in the 9i env.

May$e

[julian]

Originally posted by mayse
Hi GPSingh,

I am aware of that command for the 8i env, but I don't think it applies in this situation. I am dealing with the undo tablespace in the 9i env.

May$e

Is it Oracle managed? Do not try to shrink it if it is.

[mayse]

Hi Julian,

Yes it is Oracle managed. So what were my choices ?
How can I shrink it beside the brute force method that I used.

Thanks

[julian]

If you choose to use Oracle Managed Undo Tablespaces, then you are stuck with them. Hands off :-! Let Oracle do the shrinking for you if such is neceessary.

[mayse]

Hi Julian,

That's good to know. However, what would have to transpire for "oracle" to decide when to shrink. As I found out I could not make it shrink, even after truncating the table, bounced the d/b, dropped the user, created another undo tablespace. I basically tried everything to force oracle's undo manager to shrink it, but failed.

I'll study the other option: namely user managed and see what it offers from the management perspective.


Thanks

[julian]

You are going "deep". Do you know what a "steal extent" is?

In 9i, SMON is responsible for managing space within the undo tablespace, periodically or to use the Oracle's own terminology, proactively performing shrinks on idled undo segments.

However, SMON performs space management when foreground processes need to steal extents. Only then!

If you can and know hoe to manage your RBSs, you better make them by hand.