Re: how to shrink undo tablespace on 10GR2?

I got these instructions here in the forum in the past. It solved my problem.
I guess it can help friends that need to re-create UNDOTBS. This is a sample. Change script as you need.

conn sys/password as sysdba

Identify the current undo tablespace:

set long 1000
select dbms_metadata.get_ddl('TABLESPACE', 'UNDOTBS1') from dual;

DBMS_METADATA.GET_DDL('TABLESP

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\ORACLE\ORADATA\DB10G\UNDOTBS01.DBF' SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE 1 row selected.

Use this information to build a new undo tablespace, remember to change both the tablespace name and the datafile name. Also, don't forget to define a smaller size or there isn't much point doing this:

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE 'C:\ORACLE\ORADATA\DB10G\UNDOTBS02.DBF' SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE; Tell the system your switching to another undo tablespace:

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

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:

SHUTDOWN IMMEDIATE
STARTUP Then manually remove the datafiles associated with the initial undo tablespace. These should be listed by the query at the top.

You should now have a smaller undo tablespace.

Best Regards
Eriovaldo

On Wed, Oct 20, 2010 at 12:37 PM, Yong Huang <yong321_at_yahoo.com> wrote:

> The recommended way to "shrink" an undo tablespace in AUM is as others
> said, create a new one and assign undo_tablespace to it.
>
> If you work with Oracle support, they may allow you to set
> "_smu_debug_mode" to 4 and either shrink an undo segment, or offline
> and drop it. Just another option to keep in mind, even though it's
> probably more work.
>
> Yong Huang
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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