Shrink undo tablespace

At times you will run heavy SQL commands and your undo space will grow very big, it will never grow smaller automatically. You can do this manually by creating a new undo tablespace and removing the old one.

connect sys/pwd as sysdba
CREATE UNDO TABLESPACE undotbs4 DATAFILE 'C:/oracle/product/10.2.0/oradata/orcl/UNDOTBS04.DBF' SIZE 100M REUSE AUTOEXTEND ON;
alter system set UNDO_TABLESPACE=UNDOTBS4;
drop tablespace UNDOTBS3 including contents and datafiles;

Note: check and delete UNDOTBS1 manually on your system if needed