Until Oracle 8i, Oracle uses rollback segments to manage the undo data. Oracle9i introduced automatic undo management, which allows the dba to exert more control on how long undo information is retained, simplifies undo space management and also eliminates the complexity of managing rollback segments. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.
Space for undo segments is dynamically allocated, consumed, freed, and reused — all under the control of Oracle Database, rather than by DBA. Although both rollback segments and undo tablespaces are supported, both modes cannot be used 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 bounce the database in order to effect the switch to another method of managing undo.
System rollback segment exists in both the modes. When operating in automatic undo management mode, any manual undo management SQL statements and initialization parameters are ignored and no error message will be issued e. Automatic Undo Management. An undo tablespace must be available, into which the database will store undo records. The default undo tablespace is created at database creation, or an undo tablespace can be created explicitly.
When the instance starts up, the database automatically selects for use the first available undo tablespace. This is not recommended, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace. If the database contains multiple undo tablespaces, you can optionally specify at startup that you want an Oracle Database instance to use a specific undo tablespace.
To findout the current undo tablespace. Committed undo information normally is lost when its undo space is overwritten by a newer transaction. However, for consistent read purposes, long-running queries sometimes require old undo information for undoing changes and producing older images of data blocks.
The success of several Flashback features can also depend upon older undo information. Retention is specified in units of seconds. This value specifies the amount of time, undo is kept in the tablespace. The system retains undo for at least the time specified in this parameter. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space if retention is not guaranteed.
This action can potentially cause some queries to fail with the ORA "snapshot too old" error message. Oracle Database 10g automatically tunes undo retention by collecting database use statistics and estimating undo capacity needs for the successful completion of the queries.
Under space constraint conditions, the system may retain undo for a shorter duration than that specified by the low threshold value in order to allow DML operations to succeed.
Automatic tuning of undo retention is not supported for LOBs. 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. Retention Guarantee. Oracle Database 10g lets you guarantee undo retention. When you enable this option, the database never overwrites unexpired undo data i. This option is disabled by default, which means that the database can overwrite the unexpired undo data in order to avoid failure of DML operations if there is not enough free space left in the undo tablespace.
In order to guarantee the success of queries even at the price of compromising the success of DML operations, you can enable retention guarantee. This option must be used with caution, because it can cause DML operations to fail if the undo tablespace is not big enough. Script to estimate the amount of UNDO tablespace size required for the database:. Estimate the amount of time required for a transaction to use UNDO:.
I am also an independent Oracle DBA Consultant providing services and strategies to teams in other organizations across the globe. I love learning new technologies and very enthusiastic to train professionals on Oracle DBA.
In this way, there will be no active use of undo for a long time. We are a team with over 10 years of database management and BI experience. Hi, can yo please update the second script? Your email address will not be published. When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace. This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.
If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace. The following is a summary of the initialization parameters for automatic undo management:. When automatic undo management is enabled, if the initialization parameter file contains parameters relating to manual undo management, they are ignored.
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks.
Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.
When automatic undo management is enabled, there is always a current undo retention period , which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it.
Old committed undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired. Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity.
The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo.
If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information.
If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old er ror message. The database may overwrite unexpired undo information when tablespace space becomes low. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.
To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.
If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default. Enabling retention guarantee can cause multiple DML operations to fail.
Use with caution. Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured. If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load.
This tuned retention period can be significantly greater than the specified minimum retention period. Again, this tuned retention period can be greater than the specified minimum retention period. This view contains one row for each minute statistics collection interval over the last 4 days. Undo Retention Tuning and Alert Thresholds For a fixed size undo tablespace, the database calculates the maximum undo retention period based on database statistics and on the size of the undo tablespace.
For more information on tablespace alert thresholds, see "Managing Tablespace Alerts".
0コメント