Contents
- 1 What is the maximum extension size of undo tablespace?
- 2 How do you calculate current undo retention?
- 3 How do I reclaim space from undo tablespace?
- 4 How is the size of the undo table determined in Oracle?
- 5 How does Oracle auto manage the undo tablespace?
- 6 What to do if your undo size is less than the actual size?
What is the maximum extension size of undo tablespace?
Undo tablespaces have some additional properties beyond those of permanent tablespaces. There can be multiple undo tablespaces, but only one can be active for an Oracle instance. When you create the database using DBCA, it creates an autoextending undo tablespace named UNDOTBS1 , with a maximum extension size of 35 GB.
How do you calculate current undo retention?
For LOBs, the database attempts to honor the minimum undo retention period specified by UNDO_RETENTION . However, if space becomes low, unexpired LOB undo information may be overwritten. You can determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view.
What is the default undo retention period in Oracle 10g?
300 seconds
The default is 300 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest.
How do I reclaim space from undo tablespace?
The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I’ve used autoextend, but you may wish to remove this if you want manual control over the datafile size.
How is the size of the undo table determined in Oracle?
Given enough time, Oracle will automatically manage the UNDO usage. In Oracle, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, and the setting for these are determined by the level of DML activity in the database:
How to optimize the parameters for Oracle undo?
The UNDO_RETENTION parameter CalculateUNDO_RETENTION for given UNDO Tabespace You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity.
How does Oracle auto manage the undo tablespace?
In this management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions. You set the UNDO_MANAGEMENT initialization parameter to AUTO to enable automatic undo management. A default undo tablespace is then created at database creation.
What to do if your undo size is less than the actual size?
The previous query may return a “NEEDED UNDO SIZE” that is less than the “ACTUAL UNDO SIZE”. If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.