Resize Undo Tablespace after ORA-03297 file contains used data beyond…
Every Oracle Database must have a way of maintaining information that is used to rollback changes to the database. This information consists of records of the actions of transactions, primarily before they are committed. All these records are called undo.
When an user or application issue a rollback statement, undo records are used to undo changes that were made to the database by the transaction that were not commited.
When we have a large database, most of the long batch transactions that demand lot of undo work or purge operations will increase a lot the undo tablespace size. Depending on the size of these operations your database can have an undo tablespace from few MB to dozens GB.
To save space from an once required large undo tablespace, we may resize it to make it smaller. In my databases sometimes the size of my undo tablespace achieves 20GB.
We can resize the database datafiles with:
SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M;
Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. We can check the most high used block to check the minimum size that we can resize a particular datafile. For that we can query the dba_free_space dictionary view.
Another way to set our undo tablespace to the size that we want is to create another undo tablespace, set it the default one, take offline the old and then just drop the big old tablespace.
To check your undo tablespace info issue the following statement:
SQL> SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,’undo_tablespace’);
NAME VALUE ------------------------ undo_management AUTO undo_tablespace UNDOTBS01
SQL> CREATE UNDO TABLESPACE undotbs02
DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ SIZE 1024M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;
SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02’;
SQL> ALTER TABLESPACE undotbs01 OFFLINE;
SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
With these steps we created a new undo tablespace, set it as the system default undo tablespace and drop the old tablespace including the datafiles.