- Introduction
- How Is Undo Space Managed by Oracle9i?
- What Initialization Parameters Control Undo Space?
- Can You Use Multiple Undo Tablespaces?
- How Can You Monitor Undo Space Utilization in the SMU Mode?
- How Can Contention for Rollback Segment Be Reduced?
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
What Initialization Parameters Control Undo Space?
Answer: Undo space in Oracle9i can be managed manually by means of rollback segments or automatically by the system. The following initialization parameters control the management of undo space in an Oracle9i database:
|
Parameter |
Description |
|
UNDO_MANAGEMENT |
This parameter determines the undo space management mode in effect for the database. This parameter cannot be dynamically changed while the database instance is up and running. If set to AUTO, the SMU scheme is in effect; if set to MANUAL, the RMU scheme is in effect. The default for Oracle9i is AUTO. |
|
UNDO_TABLESPACE |
This parameter makes sense only when the database is running in the SMU mode. It specifies the undo tablespace to use in an SMU-managed Oracle9i database. The setting can be changed dynamically by using the ALTER SYSTEM command. The undo tablespace specified by this parameter should already have been created. If this parameter is omitted, the first available undo tablespace in the database is chosen; if none is available, the SYSTEM rollback segment is used. |
|
UNDO_RETENTION |
This parameter makes sense only when the database is running in the SMU mode. It specifies the length of time to retain undo. The setting can be changed dynamically by using the ALTER SYSTEM command. The default is five minutes. The setting of this parameter has a significant impact on the Oracle9i feature called "flashback query"—the ability to query data in the past. |
|
UNDO_SUPPRESS_ERROR |
When in the SMU mode, you can't execute the RMU mode type command—for example, to take a rollback segment online/offline. This parameter specifies whether error messages are to be generated if RMU SQL statements are issued while running the database in the SMU mode. The setting can be changed dynamically using the ALTER SYSTEM command. The default value of FALSE indicates that error message are not to be suppressed. |
|
ROLLBACK_SEGMENTS |
This parameter makes sense only when the database is running in the RMU mode. It specifies the rollback segments to acquire at startup. It cannot be changed dynamically. |
|
TRANSACTIONS |
This parameter specifies the maximum number of concurrent transactions allowed for this database instance. |
|
TRANSACTIONS_PER_ROLLBACK_SEGMENT |
This parameter makes sense only when the database is running in the RMU mode. It specifies the maximum number of concurrent transactions that each rollback segment is expected to handle. |
|
MAX_ROLLBACK_SEGMENTS |
This parameter makes sense only when the database is running in the RMU mode. It specifies the maximum number of rollback segments that can be online for this instance. |
|
SESSIONS |
When the database instance is running in the SMU mode, the setting of this parameter is used to derive the number of undo segments to create in an undo tablespace. |