DBA Notes: 2011/10/26

Cheng Li

Oracle Undo tablesapce management

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 in 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.

Example:

/* 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.

Reference:

http://www.360doc.com/content/11/0518/14/2054285_117665554.shtml

http://blog.csdn.net/success_dream/article/details/2357476

http://space.itpub.net/519536/viewspace-704446

http://decipherinfosys.wordpress.com/2007/06/27/re-sizing-or-switching-undo-tablespace/

http://www.dbasupport.com/oracle/ora9i/undo.shtml

http://blog.ixpub.net/html/58/12945358-419886.html