RE: How do we shrink UNDO tbs in 9i ?

Yechiel makes an excellent point, AND if you can tolerate leaving UNDO1 around a while after it is no longer active you will avoid snapshot too old errors due to quickly getting rid of relatively recent read consistent information in UNDO1. (Except in bug conditions Oracle will find rollback information in tablespaces even after they are no longer the undo tablespace as long as they are still online and part of the database.)

So. Step 1. (Wait as long as you can). Step 2. Done.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yechiel Adar
Sent: Sunday, November 18, 2007 7:11 AM
Cc: oracle-l
Subject: Re: How do we shrink UNDO tbs in 9i ?

Just want to know:
Why you need steps 3-5?
Why not leave the database with undo tablespace called UNDO2?

Adar Yechiel
Rechovot, Israel

Bala wrote:
>
> 1) alter system set undo_tablespace = UNDO2;
> 2) Drop tablespace undo1 including contents and datafile;
> 3) create original undo tablespace undo1 with small size
> 4) alter system set undo_tablespace = UNDO1;
> 5) drop the second undo tablespace ( UNDO2)
>

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l