RE: how to shrink undo tablespace on 10GR2?
- From: Upendra N <nupendra@xxxxxxxxxxx>
- To: <yong321@xxxxxxxxx>, <ecandrietta@xxxxxxxxx>
- 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.
-Upendra
> Date: Wed, 20 Oct 2010 18:02:25 -0700
> From: yong321@xxxxxxxxx
> Subject: Re: how to shrink undo tablespace on 10GR2?
> To: ecandrietta@xxxxxxxxx
> CC: mccdba1@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>
> > 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
Other related posts:
