RE: how to shrink undo tablespace on 10GR2?
Date: Wed, 20 Oct 2010 23:51:30 -0400
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.
> 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