Oracle Database Undo Tablespace Mangement.

Creating undo tablespace.

SQL> create undo tablespace undotbs2 
  2  datafile '/u01/app/oracle/oradata/orcl
     /undotbs201.dbf' size 200m
  3  autoextend on 
  4  extent management local;

Tablespace created.


Switching Undo tablespace.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1




SQL> alter system set undo_tablespace=UNDOTBS2;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

Undo Tablesapce behaviour and errors associated

with undo tablespace.


In the Undo tablespace extents can have the following status.

1) ACTIVE
2) UNEXPIRED
3) EXPIRED

When a transaction takes place in the database it will
search for Undo extents in the following manner.

1) Use a undo segment which does not have an ACTIVE extent,
   if that is not possible allocate a new segment, and finally
   if allocation is not possible due to space constarints
   return an error.

2) Look for EXPIRED extents and try to use them.

3) Use the UNEXPIRED extents.


Since in a long running query unexpired extents can be used
the database may issue the following errors.

ORA-01555: snaphot too old
ORA-30036: unable to extend segment by ... in undo
tablespace 'UNDOTBS!'

these errors can be resolved by doing the following.

1) Size the undo tablespace appropriately and make
   it autoextensible if necessary.

# Instead of making trial and error methods for resizing
# the undo tablespace, the Undo tablespace advisor can be
# used for this task through Enterprise Manager or API.
# But this feature is available from Oracle 10g onwards. 

2) Change the undo_retention parameter.

Ex:  alter system set undo_retention = 2400;

# Undo retention is measured in seconds,
# So the above query sets undo retention to 40 minutes.

3) (Oracle 10g onwards) Also to guarantee the success of
   long running transactionsretention guarantee can also be enabled.
   what this setting does is that it guarantees the undo retention.
   The database never overwrites unexpired undo data even if the
   transaction fails due to lack of space. 

   So, make sure Undo tablespace is sufficiently sized before
   using this feature.


SQL> show parameter tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
undo_tablespace                      string      UNDOTBS2

SQL> select tablespace_name, retention from dba_tablespaces
  2  where tablespace_name = 'UNDOTBS2';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS2                       NOGUARANTEE



SQL> alter tablespace undotbs2 retention guarantee;

Tablespace altered.

SQL> select tablespace_name, retention from dba_tablespaces
  2   where tablespace_name = 'UNDOTBS2';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS2                       GUARANTEE


Is your Undo Tablespace Auto Extensible ? (Applicable from 10gR2 onwards.)

According to Oracle documentation the parameter "UNDO_RETENTION" behaves differently
in the following two cases.

1) Undo Tablespace is fixed size.
2) Undo Tablespace is Auto Extensible.


1) Undo Tablespace is fixed size.

If your Undo Tablespace is fixed size, Then the parameter UNDO_RETENTION
is meaningless. Because it will be ignored by the database even if you
configure it. The database will configure the retention period
on it's own based on system activity and undo tablespace.

2) Undo tablespace is Auto Extensible

If the undo tablespace has the AUTOEXTEND option enabled, then the database
will try not to overwrite unexpired undo information within the UNDO_RETENTION
time. If there is a space issue, then instead of overwriting undo information
the tablespace will autoextend itself. If the maxsize clause is specified for
an auto-extending undo tablespace, when the tablespace reaches its maximum size,
the database will begin to overwrite unexpired undo information.


So a good strategy to avoid "snapshot too old error" might be to
Configure Undo Tablespace with AUTOEXTENSION and additionaly you may
put a limit to its growth by the MAXSIZE clause.

If You do not put MAXSIZE clause then your tablespace may grow to
following sizes with a single datafile depending upon your blocksize.

Tablespace Block Size   Max Datafile size. 

2K                      8GB
4K                      16GB
8K                      32GB
16K                     64GB
32K                     128GB