||Oracle Tips by Burleson
Chapter 5 - Oracle Database
OCP Instructors Guide for
Oracle DBA Certification
Oracle9i - Database
Managed Undo Segments
In Oracle9i, administrators have their choice
of continuing to manage rollback segments on their own (manual undo
management) or configuring the database to manage its own before
image data (automatic undo management). Oracle refers to system
managed before image segments as undo segments.
Administrators must create a tablespace to hold
undo segments by using the new UNDO keyword in the tablespace create
CREATE UNDO TABLESPACE undots1
SIZE 10M AUTOEXTEND ON;
The following initialization parameters are
used to activate automatic undo management:
– AUTO configures the database is to use automatic undo segments.
MANUAL configures the database to use rollback segments.
undo_tablespace – Specifies the tablespaces that are to be used to hold the undo
segments. The tablespace must be created using the UNDO keyword. If
no tablespace is defined, Oracle will select the first available
undo tablespace. If no undo tablespaces are present in the database,
Oracle will use the system rollback segment during startup. This
value can be set dynamically by using the ALTER SYSTEM statement.
undo_retention – specifies the amount of time that Oracle attempts to keep undo
data available. This parameter becomes important when the Oracle9i
flashback query option is used.
You cannot create database objects in undo
tablespaces. It is reserved for system-managed undo data. The view
dba_undo_extents can be
accessed to retrieve information relating to system managed undo
data. For those of us familiar with v$rollstat, it is still available and the information reflects the behavior of
the undo segments in the undo tablespace.
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
by Christopher T. Foot
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA