Undo Tablespaces in Oracle9i
September 17, 2001
Oracle Database keeps records of actions of transactions before they are committed and Oracle needs this information to rollback or Undo the Changes to the database. These records in Oracle are called Rollback or Undo Records. These records are used to Rollback transactions when a rollback statement is issued or during recovery of a database or to provide a read consistent view of data.
Until Oracle 8i and also in 9i, Oracle uses Rollback Segments to manage the Undo Data. Starting with Oracle 9i, DBA's are provided with some a new feature referred to as "Undo Tablespace" which allows the dba to exert more control on how long Undo information is retained and also eliminates the complexity of managing Rollback Segments in certain environments.
Starting with 9i, the rollback segment way is referred to as Manual Undo Management Mode and the new Undo Tablespaces method as the Automatic Undo Management Mode. Although both rollback Segments and Undo Tablespaces are supported in Oracle 9i, Both modes cannot be used. System Rollback segment exists in both the modes. Since we are all familiar with the manual mode, lets look at the features of the Automatic Undo Management (Undo Tablespaces )
Init.ora Parameters for Automatic Undo Management
UNDO_MANAGEMENT : This parameter sets the mode in which oracle manages the Undo Information.
The default value for this parameter is MANUAL so that all your old init.ora files can be used without any changes. To set the database in an automated mode, set this value to AUTO. ( UNDO_MANAGEMENT = AUTO)
UNDO_TABLESPACE : This parameter defines the tablespaces that are to be used as Undo Tablespaces. If no value is specified oracle grabs the first available Undo Tablespace or if there are none present, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online ( UNDO_TABLESPACE = undo_tbs1 )
UNDO_RETENTION : This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past. Default value is 900 Secs ( UNDO_RETENTION = 500)
UNDO_SUPRESS_ERRORS : This is a good thing to know about in case your code has the alter transaction commands that perform manual undo management operations. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.
Creating and Managing Undo Tablespaces :
Undo tablespaces use syntax that is similar to regular tablespaces except that they use the keyword UNDO. These tablespaces can be created during the database creation time or can be added to an existing database using the create UNDO Tablespace command
All operations like Renaming a data file, Adding a datafile, Online /Offline Swith or Start Backup / End Backup Switch can be made using the regular alter tablespace command. All other operations are managed by Oracle in the automated management mode.
v$UNDOSTAT: This view contains statistics for monitoring the effects of transaction execution on Undo Space in the current instance. These are available for space usage, transaction concurrency and length of query operations. This view contains information that spans over a 24 hour period and each row in this view contains data for a 10 minute interval specified by the BEGIN_TIME and END_TIME.