Resize undo tablespace

You created undo tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.

Your have just done purging (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.

You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.

So you should drop and recreate undo tablespace using the following commands:

1, Connect to Oracle

sqlplus /nolog
connect / as sysdba

2, Find out which undo tablespace is being used:

SQL>select name,value from v$parameter where name in ('undo_management','undo_tablespace');

NAME VALUE
——————- ——————-
undo_management AUTO
undo_tablespace UNDOTBS1

3, Create new undo tablespace UNDOTBS2 with smaller size.

SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/edr/undotbs02.dbf' size 500m reuse;


4, Tell Oracle to use new undo tablespace.

SQL> alter system set undo_tablespace=UNDOTBS2;

5, Now you can safely drop and recreate tablespace UNDOTBS1.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

6, SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/edr/undotbs01.dbf' SIZE 250m reuse AUTOEXTEND ON ;

Tablespace created.

7, SQL> alter system set undo_tablespace=UNDOTBS1;

8, Now you can safely drop tablespace UNDOTBS2.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.