Home > Articles > Data > Oracle

  • Print
  • + Share This
From the author of

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.


  • + Share This
  • 🔖 Save To Your Account