Resize Undo Tablespace after ORA-03297 file contains used data beyond…

February 12, 2009  |  dba

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>
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;
SQL>

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.


8 Comments


  1. Danielle Austin

    Awesome… JUST what I wanted to know. I wish Oracle’s documentation was as easy to search as this google search was! I accomplished in about 5 min. with your search, what I’d already wasted about 1/2 hr. trying to find via Oracle’s online documentation. eeeeeeee

  2. Great info! Thank you very much– I, also, spent too much time in a futile search until I found your instructions which did exactly what I needed.

    I noticed (on Win2003) that I had to then shut down the database and manually delete the file.

    Also, the undo tablespace on my system was using only 91 M, so I set initial size to 512 MB and max size to 1024 M (both smaller than your values– but I’m working in a VM with only 20 GB). Any rules of thumb for what these numbers *should* be???

  3. Thank you so much! Just what I was looking for and simple to follow. Tried all these other things from Oracle website and other websites and ran into more errors and problems.

    Thanks again!!!

  4. Isn’t that an oracle bug? If at the end you are able to drop UNDOTBS01 tablespace but not shrink it, then something is wrong with Oracle itself, isn’t it?

  5. WhAoOo… Great….
    This is what I was looking for since a couple of days. Following your instructions, it took not moore that 2 min to sort out the issue. Thanks a million…

  6. do i need to shutdown my application before doing this or i can straight away pointing to my 2nd undotbs without downtime

  7. This is wrong you cannot drop a undo tablespace straight away.
    What abt the data already existing in UNDOTBS1. Does this mean all data’s are commited and no informationis required from the old UNDO_TABLESPACE.

    Thanks,

Leave a Reply