Undo Tablespace

Undo Consists records of the actions of transactions, primarily before they are committed. These information is used to rollback, or undo, changes to the database.

Undo records are used to:

• Roll back transactions when a ROLLBACK statement is issued 
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Oracle Flashback Query
• Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the data files. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Undo_Retention:  A database can use only one active Undo TS at a time. Undo data must be retain till commit or rollback the transaction. After the completion of transaction it may overwrites the previous instructions. It is done automatically by Oracle only. In this case we can retain the data by using Undo_Retention time.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled          boolean     TRUE
undo_management            string        AUTO
undo_retention                  integer      900

undo_tablespace               string        APPS_UNDOTS1


SQL> create undo tablespace undots2 datafile '/u03/oravis/VIS/data/undots2.dbf'  size 50m autoextend on;


Tablespace created.

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='UNDOTS2';                     
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u03/oravis/VIS/data/undots2.dbf

UNDOTS2

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled                    boolean     TRUE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      APPS_UNDOTS1

SQL> alter system set undo_tablespace=undots2 scope=both;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTS2