UNDO Tablespace

Undo Segment

An undo segment is used to save the old value (undo data) when a process changes data in a database. It stores the location of the data and the data as it existed before being modified. The header of an undo segment contains a transaction table where information about the current transactions using the undo segment is stored.
A serial transaction uses only one undo segment to store all of its undo data.
Many concurrent transactions can write to one undo segment.

Initialization Parameter
If AUTO, use automatic undo management. The default is MANUAL.

An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

Transaction Rollback
When a transaction modifies a row in a table, the old image of the modified columns (undo data) is saved in the undo segment. If the transaction is rolled back, the Oracle server restores the original values by writing the values in the undo segment back to the row.

Transaction Recovery
If the instance fails while transactions are in progress, the Oracle server needs to undo any uncommitted changes when the database is opened again. This rollback is part of transaction recovery. Recovery is possible only because changes made to the undo segment are also protected by the redo log files.

Read Consistency
While transactions are in progress, other users in the database should not see any uncommitted changes made by these transactions. In addition, a statement should not see any changes that were committed after the statement begins execution. The old values (undo data) in the undo segments are also used to provide the readers a consistent image for a given statement.

Read Consistency
The Oracle server guarantees that a statement sees data from a consistent time, even if that data is modified by other transactions.
When the Oracle server begins executing a SELECT statement, it determines the current system change number (SCN) and ensures that any changes not committed before this SCN are not processed by the statement. Consider the case where a long-running query is executed at a time when several changes are being made. If a row has changes that were not committed at the start of the query, the Oracle server constructs a read-consistent image of the row by retrieving the before image of the changes from the undo segment and applying the changes to a copy of the row in memory.

TYPES of UNDO Segment

• SYSTEM: Used for objects in the SYSTEM
• Non-SYSTEM: Used for objects in other
– Auto Mode: Requires an UNDO tablespace
– Manual Mode:
– Private: Acquired by a single instance
– Public: Acquired by any instance
• Deferred: Used when tablespaces are taken offline
immediate, temporary, or for recovery

Dropping an UNDO Tablespace
• The DROP TABLESPACE command drops an UNDO
• An UNDO tablespace can only be dropped if it is
currently not in use by any instance.
• To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions
are complete

You can drop tablespace UNDOTBS after all transactions within the tablespace are complete.
To determine whether any active transactions exists use the following query:
SQL> SELECT a.name,b.status
FROM v$rollname a, v$rollstat b
WHERE a.name IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS’
)  AND a.usn = b.usn;
NAME                        STATUS
————————- —————

Automatic Undo Management:

     Set to TRUE, this parameter suppresses errors while attempting to  execute  manual operations in AUTO mode.
Controls the amount of undo data to retain for consistent read

Determines how long to retain undo data to provide for consistent reads. Retaining undo data longer allows for longer queries and also requires larger data files for the UNDO tablespace.
The UNDO_RETENTION parameter, defined in seconds, can be set in the initialization file or modified dynamically with an ALTER SYSTEM command.


A value of 900 retains undo data for 15 minutes.

Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires
three pieces of information
• (UR) UNDO_RETENTION in seconds
• (UPS) Number of undo data blocks generated per second
• (DBS) Overhead varies based on extent and file size (db_block_size)

        UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
            FROM v$parameter
            WHERE name = ‘undo_retention’),
      (SELECT (SUM(undoblks)/SUM(((end_time –
            begin_time)*86400))) AS UPS
            FROM v$undostat),
      (SELECT value AS DBS
            FROM v$parameter
            WHERE name = ‘db_block_size’);

Divide the result by 1048576 to get size in MB.

Data Dictionary Views

• Dynamic Performance Views

SELECT segment_name,owner,tablespace_name,status
FROM dba_rollback_segs;

——————— ———–   ——————-  ——
SYSTEM               SYS          SYSTEM         ONLINE
_SYSSMU1$     PUBLIC     UNDO1          ONLINE

Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.

SELECT n.name, s.extents, s.rssize,s.hwmsize,
s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;


To check the use of a undo segment by currently active transactions, join the

SELECT s.username, t.xidusn, t.ubafil,  t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: