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

Your have just done puring (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 sys/xxx@crm as sysdba

2, Find out which undo tablespace is being used:

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 K:\oradata\CRM\UNDOTBS2_01.DBF' size 1000m 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;

Tablespace dropped.

SQL> create undo tablespace UNDOTBS1 datafile ‘K:\oradata\CRM\UNDOTBS1_01.DBF’ size 2000m reuse;

Tablespace created.