Systems Engineering and RDBMS

Re-Sizing or Switching UNDO tablespace

Posted by decipherinfosys on June 27, 2007

If you have a large database, then during the purge operation or large batch transactions that involve a lot of undo work, the undo tablespace will grow rapidly and will occupy a large amount of space on the file system.  In order to avoid the ORA-1651 error (unable to extend undo segment by <n> in tablespace <undo_tablespace>) which could result because of this expansion,  it is a very common practice that you add a set of data files to the undo tablespace with AUTOEXTEND ON MAXSIZE UNLIMITED.  And if you try to re-size the data-file, you may encounter ORA-3297 error: file contains used data beyond the requested RESIZE value.

Solution in this situation is to create a NEW UNDO tablespace with smaller size and switch over to your current UNDO tablespace to NEW UNDO tablespace.


/* Create new undo tablespace with smaller size */
SQL> create undo tablespace undotbs2 datafile ‘/u01/oradata/decipher/undotbs2_01.dbf’ size 1024m autoextend on next 256m maxsize 10240m;

/* Set new tablespace as undo_tablespace */
SQL> alter system set undo_tablespace= UNDOTBS2 scope=both;

If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This is normally to un-assign an undo tablespace in the event that you want to revert to manual undo management mode.

/* Drop the old tablespace */
SQL> drop tablespace UNDOTBS1 including contents;

Dropping the old undo tablespace may give ORA-30013 error: undo tablespace ‘%s’ is currently in use. This error indicates that you need to wait for the undo tablespace to become OFFLINE.  Initially, on switching to the new UNDO tablespace, the old UNDO tablespace will go into PENDING OFFLINE status until the active incomplete transactions are finished.

Few other important points:

•    The value for undo_retention also has a role in growth of the undo tablespace. If there is no way to get the undo space for a new transaction, then the undo space will be re-used first by overwriting all EXPIRED undo and then ignoring the value for undo_retention till you have sufficient UNEXPIRED (Obviously INACTIVE) undo segment provided that UNDO tablespace is not created with retention guaranteed option. But, if the data-files for the undo tablespace are set to auto extensible, it will not re-use the space. In such scenarios, a new transaction will allocate space and your undo tablespace will start growing.
•    Is the large file size really bad?
Overhead on larger file/tablespaces can impact the database and the OS. Also with the bigger file/tablespace you will have other overheads, like backup will take longer if you are not using RMAN. As far as the undo management there should be no performance impact just because the file/tbs is bigger.

3 Responses to “Re-Sizing or Switching UNDO tablespace”

  1. […] Performance tuning an Oracle database will always stay a hot topic. This week I found a number of articles; Tanel Poder writes about session level statspack. Coskan Gundogar talks about sub latch wait events and finally Jeremy Schneider has a very interesting part 1 article about statspack and AWR latch waits. Hang around for the second part. Another DBA topic this week was a reminder to check your local_listener parameter when using RAC by Paul Gallagher. Decipher completes the list with an example to resize a undo tablespace by switching tablespaces. […]

  2. kvik lån said

    kvik lån…

    Re-Sizing or Switching UNDO tablespace « Systems Engineering and RDBMS…

  3. More Info said

    More Info

    Re-Sizing or Switching UNDO tablespace « Systems Engineering and RDBMS

Sorry, the comment form is closed at this time.

%d bloggers like this: