Managing Undo Data in Oracle

Managing Undo Data

 Automatic Undo Management
 Manual Undo Management

Undo Segment

 A serial transaction uses only one undo segment to store all of its undo data.
 Many concurrent transactions can write to one undo segment.

Transaction Read Consistency
Read consistency is always provided for a SQL statement. However, you can request read consistency for a read-only transaction by issuing the following command at the beginning of the transaction:

SET TRANSACTION READ ONLY;

Or, you can request read consistency for a transaction performing DML by issuing the following command at the beginning of the transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

In either case, the Oracle server provides data that is read consistent from the start of the transaction. Using SERIALIZABLE can have a negative impact on performance.

Types of Undo Segments

• SYSTEM: Used for objects in the SYSTEM tablespace
• Non-SYSTEM: Used for objects in other tablespaces:
– 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

Automatic Undo Management: Concepts

Undo segments are created with the naming convention:
_SYSSMUn$
For example:
_SYSSMU1$
_SYSSMU2$
Automatic Undo Management:Configuration

• Configure two parameters in the initialization file:
– UNDO_MANAGEMENT
– UNDO_TABLESPACE

EX:
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS

UNDO_MANAGEMENT

 UNDO_MANAGEMENT cannot be changed dynamically after the database starts

UNDO_TABLESPACE

 altered dynamically using the ALTER SYSTEM

ALTER SYSTEM SET undo_tablespace = UNDOTBS;
Creationing a undo tablespace:

SQL > CREATE UNDO TABLESPACE undo1 DATAFILE 'undo1db01.dbf' SIZE 20M;

 During database creation, if the UNDO_MANAGEMENT parameter is set to AUTO and you omit the UNDO tablespace clause from the CREATE DATABASE statement then the Oracle server creates an UNDO tablespace with the name SYS_UNDOTBS.
 The default data file, for data file tablespace SYS_UNDOTS, will have the name 'dbu1.dbf' located in $ORACLE_HOME/dbs, the size is operating system dependent, and AUTOEXTEND is ON.

SQL > ALTER TABLESPACE undotbs ADD DATAFILE 'undotbs2.dbf' SIZE 30M AUTOEXTEND ON;

Dropping an UNDO Tablespace

• To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions are complete

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

SQL > DROP TABLESPACE UNDO1 including contents and datafiles cascade constraints;