RE: how to shrink undo tablespace on 10GR2?

From: Upendra N <nupendra_at_hotmail.com>
Date: Wed, 20 Oct 2010 23:51:30 -0400
Message-ID: <BLU129-W11B3F4E9783BDF5A4DEF01D85D0_at_phx.gbl>

You need to switch all the users to the new UNDO tablespace and only after the completion of any active transactions on the old tablespace, you'll be able to drop the it. The following query will identify any pending transactions on the old UNDO. column username format a10
SELECT a.name,b.status , d.username , d.sid , d.serial# FROM v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTBS1'
		 );

If you really can't wait until the transaction completes, you may want to kill it.

-Upendra

> Date: Wed, 20 Oct 2010 18:02:25 -0700
> From: yong321_at_yahoo.com
> Subject: Re: how to shrink undo tablespace on 10GR2?
> To: ecandrietta_at_gmail.com
> CC: mccdba1_at_yahoo.com; oracle-l_at_freelists.org
>
> > Drop the original undo tablespace:
> >
> > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
> >
> > Especially on Windows, you might find the datafiles associated with
> > the undo tablespace remain on disk. If so, restart the instance:
>
> How could that be possible? DROP TABLESPACE failed with an error?
>
> Yong Huang
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
                                               

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

Received on Wed Oct 20 2010 - 22:51:30 CDT