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;

Code:
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;

Code:
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 'UNDOTBS1'

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 transactions retention 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;
Code:
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
undo_tablespace                      string      UNDOTBS2
SQL> select tablespace_name, retention from dba_tablespaces
2 where tablespace_name = 'UNDOTBS2';
Code:
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';

Code:
TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS2                       GUARANTEE