To shrink (re-size) undo tablespace in Oracle DBMS, please follow the following steps:

1. Create a temporary tablespace for swapping

CREATE undo TABLESPACE undotbs_2 DATAFILE
‘/orad001/data_files/undotbs2_02.dbf’ size 10000M;

2. Use it as default UNDO tablespace

ALTER SYSTEM SET undo_tablespace=undotbs_2;

3. Drop the old UNDO tablespace

DROP TABLESPACE UNDOTBS1 including contents;

****** if you face an error that you can’t drop the undo tablespace then you need to check the sessions that requires (commit or rollback).

you may face the error “Undo Tablespace X moved to Pending Switch-Out state”

To Fix this use the following query to check the session,oracleuser, program that requires either a commit transaction or a rollback.

**********************
SELECT
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status=’PENDING OFFLINE’;
**************************

4. Create back the original UNDO tablespace

CREATE undo TABLESPACE UNDOTBS1 DATAFILE
‘/orad001/data_files/undo01.dbf’ size 5000M REUSE,
‘/orad001/data_files/undo02.dbf’ size 5000M REUSE;

5. Set the default UNDO tablespace back to the original one

ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

6. Drop the temporary one

DROP TABLESPACE undotbs2 including contents and datafiles;

7. do verification using this command:

SQL> show parameter undo_tablespace