unsure of how to handle problematic undotbs

Hi,

I have just started a DBA job to maintain oracle 9i database on RHAS3.0. One of the databases has a huge UNDOTBS1 that doesn't seem to shrink by itself. The size of UNDOTBS1 now is more than 60GB which is totally ridiculous!

What is the safe way to shrink the undotbs ? I have searched the internet and other people seems to suggest that I create and use UNDOTBS2 temporarily, then totally drop UNDOTBS1 and then recreate and use UNDOTBS1.

But I am not confident enough to totally drop the UNDOTBS1 and what I did instead was, create and use UNDOTBS2, then I put UNDOTBS1 offline. I would then observe the database for a while, if everything looks normal, only then will I totally drop the problematic UNDOTBS1.

As soon as I put UNDOTBS1 offline, my users were unable to write to the database. I guest it is still pointing to UNDOTBS1. However, at the same time I detect an increase usage in UNDOTBS2, so I know it is pointing to UNDOTBS2. When I turn UNDOTBS1 back online everything is back to normal! What's going on?

My objective is of course to drop UNDOTBS1 and reclaim the space. How do I do this without dropping UNDOTBS1 immediately. There is no particular need for me to use the name UNDOTBS1, and I will be satisfied if I have to use UNDOTBS2 from now on. I just want to get back the space. Please advise.

-Ujang Received on Mon Jul 24 2006 - 00:01:13 CDT