UNDO tablespace can grow quickly if long and uncommitted transactions are executed, sometime even take out the whole hard disk and crash the database, and lastly database itself not even can be started up.
To reduce the size of the data file (especially when it has taken all your hard disk space), we need to recreate the default UNDO tablespace because the resize data file statement will not work:
alter database datafile 'undo.bdf' resize 100M;
- Log in as sysdba
connect / as sysdba
- Start database in nomount state
- Create temporary UNDO tablespace in anywhere got free space
create undo tablespace undo2 datafile '/tmp/undo2.dbf' size 50M autoextend off;
- Set the temporary UNDO tablespace as default UNDO tablespace
alter system set undo_tablespace = undo2;
- Drop the original UNDO tablespace.
drop tablespace undo1 including contents and datafiles;
- Recreate the original UNDO tablespace.
create undo tablespace undo1 datafile '/u01/database/undo1.dbf' size 500M autoextend on next 5M maxsize 1000M;
- Set back the original UNDO tablespace to default UNDO tablespace
alter system set undo_tablespace = undo1;
- Drop the temporary UNDO tablespace
drop tablespace undo2 including contents and datafiles;