Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Addition of Rollback Segments

Another database structure is the ROLLBACK segment. Under pre-9i versions and in 9i with manually managed undo, ROLLBACK segments can be placed in any tablespace, but it is suggested that they be placed in a tablespace that contains only other rollback segments. This makes administration easier. Rollback segments can be PUBLIC, which means that for a multi-instance database, any instance can use the rollback segment, or PRIVATE, which means only the instance that has the rollback segments named in the ROLLBACK SEGMENTS clause of its INIT.ORA file can use the rollback segment.       

Under Oracle, an UNDO tablespace can be created (is created by default) and should be used to allow Oracle to internally manage UNDO (read rollback) operations. The UNDO tablespace is usually created with the UNDO TABLESPACE clause of the CREATE DATABASE command or can be added after database creation with the CREATE UNDO TABLESPACE command and addition of the required initialization parameters.

Managing the UNDO Tablespace

There are several initialization parameters that control the UNDO tablespace feature of Oracle. They are: 

UNDO_MANAGEMENT. Can be set to AUTO or MANUAL. In AUTO, the database manages undo operations; under MANUAL, the DBA manages undo operations. The default value is MANUAL, and any UNDO_TABLESPACE specification is ignored. If set to MANUAL, management of rollback segments is identical to pre-9i rollback segment management.  

UNDO_TABLESPACE. A dynamic parameter that specifies which tablespace to use as an UNDO tablespace.  

UNDO_RETENTION. A dynamic parameter that specifies the length of time to retain undo information for use in flashback queries; the default is 900 seconds.

UNDO_SUPPRESS_ERRORS. Either TRUE or FALSE. Used to suppress error messages if manual undo management SQL commands are entered in auto undo management mode.    

Creation of the UNDO Tablespace      

As stated above, the UNDO tablespace can be created either when the database is created, using the UNDO TABLESPACE clause of the CREATE DATABASE command, or it an be added later, using the CREATE UNDO TABLESPACE command and the appropriate initialization parameters set for it. An example UNDO tablespace creation command is shown here:      

CREATE UNDO TABLESPACE undo_tbs1 DATAFILE '/oracle3/oradata/galinux1/data/undo_tbs2.dbf' SIZE 200M AUTOEXTEND ON;      

Oracle determines the other attributes of the undo tablespace automatically; you only need to specify the datafile clause and size information. The Oracle system will automatically assign an UNDO tablespace with locally managed, auto-allocated extents; the maximum size will be set at approximately one and a half times the specified size. You can specify the autoextend parameters and the extent management parameters in the CREATE UNDO command, but options such as LOGGING will cause an error.

Altering an UNDO Tablespace

An UNDO tablespace can only be altered for the following items:

* Adding a datafile.

* Renaming a datafile.

* Bringing a datafile on- or offline.

* Beginning or ending backup.

* Resizing a datafile.     

Dropping an UNDO Tablespace    

The UNDO tablespace(s) are dropped using the DROP TABLESPACE command. The UNDO tablespace to be dropped cannot have any currently active transactions.

See Code Depot


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.