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.

Cheers

Tim...