Re: unsure of how to handle problematic undotbs

ujangmz_at_gmail.com wrote:
> 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

On what basis have you reached this conclusion? Do you know what UNDO RETENTION is set to? Do you know the size and timing of the longest query? Do you understand FLASHBACK QUERIES?

60GB is not totally ridiculous unless you have the metrics to back up your assertion. My recommendation, based on what you've written, is to spend your spare time reading Jonathan Lewis' books.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org