Each
database activity is recorded in the Oracle Server. Deleted data will be
temporarily stored in a predefined memory area called Undo Tablespace. This
undo data is used for undoing any user action or to recover the database from
failures.
Understanding Undo Segments
Undo
segments, also known as rollback segments, are similar to other segments in the
database, such as table or index segments, in that an undo segment is made up
of extents, which in turn are made up of data blocks. Also, an undo segment
contains data similar to that stored in a table.
But Undo
segments must be stored in a special type of tablespace called an undo
tablespace. Only one undo tablespace can be active at any one time. Undo
segments contain undo information about one or many tables involved in a
transaction. Also, undo segments automatically grow and shrink as needed,
acting as a circular buffer— transactions that fill up the extents in an undo
segment can wrap around to the beginning of the segment if the first extent is
not being used by an active transaction.
Any changes
to any table in the transaction are recorded in the assigned undo segment. The
names of the current active undo segments can be retrieved from the dynamic
performance view V$ROLLNAME.
Using Undo Data
Undo data
is the old value of data when a process or a user changes data in a table or an
index. Undo data serves four purposes in an Oracle database:
User
rollback of a transaction Read consistency of DML operations and queries
Database recovery operations Flashback functionality
User Transaction Rollback
In a
transaction, at the user level, you might have one or hundreds of DML commands
(such as DELETE, INSERT, UPDATE, or MERGE) within a particular transaction that
needs to be undone by a user or a process.
Undoing the
changes within a transaction is called rolling back part or all of the
transaction. The undo information needed to roll back the changes is called,
appropriately, the rollback information and is stored in a special type of
tablespace called an undo tablespace.
When an
entire transaction is rolled back, Oracle undoes all the changes since the
beginning of the transactions, using the saved undo information in the undo
tablespace, releases any locks on rows involved in the transaction, and ends
the transaction.
If a
failure occurs on the client or the network, abnormally terminating the user’s
connection to the database, undo information is used in much the same way as if
the user explicitly rolled back the transaction, and Oracle undoes all the
changes since the beginning of the transaction, using information saved in the
undo tablespace.
Read Consistency
Undo also
provides read consistency for users who are querying rows involved in a DML
transaction by another user or session. When one user starts to make changes to
a table after another user has already begun a query against the table, the
user issuing the query will not see the changes to the table until after the
query has completed and the user issues a new query against the table. Undo
segments in an undo tablespace are used to reconstruct the data blocks
belonging to the table to provide the previous values of the rows for any user
issuing SELECT statements against the table before the DML statements’
transaction commits.
INSERT
statements use little space in an undo segment; only the pointer to the new row
is stored in the undo tablespace. To undo an INSERT statement, the pointer
locates the new row and deletes it from the table if the transaction is rolled
back.
Configuring the Undo Tablespace
Manual undo
management is not recommended, although it is still the default in Oracle 10g;
use manual undo management only for compatibility with Oracle 8i or earlier. To
configure automatic undo management, use the initialization parameters
UNDO_MANAGEMENT, UNDO_ TABLESPACE, and UNDO_RETENTION.
UNDO_MANAGEMENT
The
parameter UNDO_MANAGEMENT specifies the way in which undo data is managed in
the database: either manually using rollback segments or automatically using a
single tablespace to hold undo information.
The allowed
values for UNDO_MANAGEMENT are MANUAL and AUTO. To change the undo management
mode, you must restart the instance. This parameter is not dynamic.
UNDO_TABLESPACE
The
parameter UNDO_TABLESPACE specifies the name of the undo tablespace to use for
read consistency and transaction rollback.
UNDO_RETENTION
The
parameter UNDO_RETENTION specifies, in seconds, how long undo information that
has already been committed should be retained until it can be overwritten.
