Undo Tablespace instead of Rollback Segments
Historically, Oracle has used rollback segments to store undo. Space
management for these rollback segments has proven to be quite complex.
Oracle now offers another method of storing undo that eliminates the
complexities of managing rollback segment space, and enables DBAs to
exert control over how long undo is retained before being overwritten.
This method uses an undo tablespace.
You cannot use both methods in the same database instance, although for
migration purposes it is possible, for example, to create undo
tablespaces in a database that is using rollback segments, or to drop
rollback segments in a database that is using undo tablespaces.
However, you must shutdown and restart your database in order to effect
the switch to another method of managing undo.
Rollback Segment in SYSTEM Tablespace
Oracle always uses a SYSTEM rollback segment for performing system
transactions. There is only one SYSTEM rollback segment and it is
created automatically at CREATE DATABASE time and is always brought
online at instance startup. You are not required to perform any
operations to manage the SYSTEM rollback segment.
Create a Database with an Undo Tablespace
You can create a specific undo tablespace using the UNDO TABLESPACE
clause of the CREATE DATABASE statement.
CREATE DATABASE VEN1
CONTROLFILE REUSE
MAXDATAFILES 256
MAXINSTANCES 4
MAXLOGFILES 62
MAXLOGMEMBERS 5
MAXLOGHISTORY 1600
CHARACTER SET "UTF8"
NATIONAL CHARACTER SET "UTF8"
DATAFILE '/u01/sys/VEN1_sys1.dbf' SIZE 300M REUSE
UNDO TABLESPACE undo DATAFILE '/u01/sys/VEN1_undo1.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tmp
TEMPFILE '/u01/tmp/VEN1_tmp1.dbf' SIZE 512064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
LOGFILE GROUP 1 ('/u01/rdo/VEN1_log1A.rdo',
'/opt/rdo/VEN1_log1B.rdo') SIZE 10M REUSE,
GROUP 2 ('/u01/rdo/VEN1_log2A.rdo',
'/opt/rdo/VEN1_log2B.rdo') SIZE 10M REUSE,
GROUP 3 ('/u01/rdo/VEN1_log3A.rdo',
'/opt/rdo/VEN1_log3B.rdo') SIZE 10M REUSE,
GROUP 4 ('/u01/rdo/VEN1_log4A.rdo',
'/opt/rdo/VEN1_log4B.rdo') SIZE 10M REUSE,
GROUP 5 ('/u01/rdo/VEN1_log5A.rdo',
'/opt/rdo/VEN1_log5B.rdo') SIZE 10M REUSE,
GROUP 6 ('/u01/rdo/VEN1_log6A.rdo',
'/opt/rdo/VEN1_log6B.rdo') SIZE 10M REUSE;
In the ALERT log, you can see, that a SYSTEM Rollback segment is
created:
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
Several Undo Segments are automatically allocated
CREATE UNDO TABLESPACE UNDO DATAFILE '/u01/sys/VEN1_undo1.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
Fri Jul 27 08:56:49 2001
Created Undo Segment _SYSSMU1$
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace
Completed: CREATE UNDO TABLESPACE UNDO DATAFILE
Obsolete INIT.ORA parameters when using an Undo Tablespace
- ROLLBACK_SEGMENTS
Specifies the rollback segments to be acquired at start up.
- TRANSACTIONS
Specifies the maximum number of concurrent transactions.
- TRANSACTIONS_PER_ROLLBACK_SEGMENT
Specifies the number of concurrent transactions that each rollback
segment
is expected to handle
- MAX_ROLLBACK_SEGMENTS
Specifies the maximum number of rollback segments that can be
online for any instance.
New INIT.ORA parameters when using an Undo Tablespace
- UNDO_MANAGEMENT
If AUTO, use automatic undo management mode. If MANUAL, use manual undo
management mode.
- UNDO_TABLESPACE
A dynamic parameter specifying the name of an undo tablespace to use.
- UNDO_RETENTION
A dynamic parameter specifying the length of time to retain undo.
Default is 900 seconds.
- UNDO_SUPPRESS_ERRORS
If TRUE, suppress error messages if manual undo management SQL
statements are issued when operating in automatic undo management mode.
If FALSE, issue error message. This is a dynamic parameter
INIT.ORA Example
### System Managed Undo
### -------------------
undo_management = AUTO
undo_retention = 10800
undo_tablespace = undo
More Information can be found in the Oracle9i Database Administrator's
Guide
Release 1 (9.0.1) "Managing Undo Space".
New Space Management in Locally Managed Tablespaces
Prior to Oracle8i, all tablespaces were created as dictionary-managed.
Dictionary-managed tablespaces rely on SQL dictionary tables to track
space utilization. Beginning with Oracle8i, you can create locally
managed tablespaces, which use bitmaps (instead of SQL dictionary
tables) to track used and free space.
Beginning with Oracle9i you can specify how free and used space within
a segment is to be managed. Your choices are:
- MANUAL
Specifying this keyword tells Oracle that you want to use free lists
for managing free space within segments. Free lists are lists of data
blocks that have space available for inserting rows. MANUAL is the
default.
- AUTO
This keyword tells Oracle that you want to use bitmaps to manage the
free space within segments. A bitmap, in this case, is a map that
describes the status of each data block within a segment with respect
to the amount of space in the block available for inserting rows. As
more or less space becomes available in a data block, its new state is
reflected in the bitmap. Bitmaps allow Oracle to manage free space more
automatically, and thus, this form of space management is called
automatic segment-space management.
Free lists have been the traditional method of managing free space
within segments. Bitmaps, however, provide a simpler and more efficient
way of managing segment space. They provide better space utilization
and completely eliminate any need to specify and tune the PCTUSED,
FREELISTS, and FREELISTS GROUPS attributes for segments created in the
tablespace. If such attributes should be specified, they are ignored.
The following statement creates tablespace users with automatic
segment-space management:
CREATE TABLESPACE users
DATAFILE '/u01/VEN1_users1.dbf' SIZE 10304K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
Limitations
- It is not possible to create a temporary tablespace with automatic
space
management:
- CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/VEN1_temp1.dbf' SIZE 512064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
ERROR at line 4:
ORA-30573: AUTO segment space management not valid for this type of
tablespace
- Your specification at tablespace creation time of your method for
managing available space in segments, applies to all segments
subsequently created in the tablespace. Also, your choice of method
cannot be subsequently altered.
- Only permanent, locally managed tablespaces can specify automatic
segment-space management.
- For LOBs, you cannot specify automatic segment-space management.
Best Regards,
Terminator
shirleyr_at_mailings.com wrote:
> Hi,
>
> I did most of my work on Oracle 8i using rollback segments. I just
> took a new job where they didn't have a DBA before and are using Oracle
> 9i and 10g. They have Undo tablespaces instead of rollback segments.
> Does undo tablespaces work like rollback segements? Can someone point
> me to some reading to explain how they should be set up and how they
> work. We have a long transaction that has been running at night and it
> gets 'snapback too old' error message. But I don;t know how the undo
> is working. Should I remove all the undofile to have just one undofile
> to use all the space for this transaction?
Received on Fri Aug 26 2005 - 07:09:01 CDT
