The “ORA-30036: unable to extend segment by 8 in undo tablespace” error message in Oracle databases indicates that the undo tablespace has run out of space and can’t be extended to accommodate the additional undo data that needs to be generated.
The undo tablespace is used by the database to store undo data, which is required to support transactions and to provide read consistency to users. When a transaction is started, the database generates undo data to record the changes made to the data. This undo data is then used to revert the changes in case of a transaction rollback.
If the undo tablespace runs out of space, the database will be unable to generate the required undo data, and the “ORA-30036: unable to extend segment by 8 in undo tablespace” error will be raised. This can result in the transaction being rolled back, or the database may become unavailable.
This error can be caused by the following events or SQL queries:
- Large transactions: Transactions that generate a large amount of undo data, such as data load operations, can quickly consume all available space in the undo tablespace.
- Long-running transactions: Transactions that run for a long time without committing or rolling back can consume a large amount of undo space.
- High undo usage: Applications that generate a large amount of undo data, such as data warehousing or OLAP applications, can consume a significant amount of undo space.
- Poor database design: Database designs that generate a large amount of undo data, such as frequent updates or deletes, can consume a significant amount of undo space.
- Improper undo retention time: Setting the undo retention time too high or low can cause the undo data to be retained too longer for rogue queries or for too small a time.
- Insufficient size of undo tablespace: If the undo tablespace is too small to accommodate the amount of undo data being generated, the “ORA-30036: unable to extend segment by 8 in undo tablespace” error may occur.
Some methods to resolve this issue can be:
Increase the undo tablespace size by adding more files:
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/path/to/undotbs1_02.dbf' SIZE 100M;
Increase the autoextend setting for the undo tablespace:
ALTER DATABASE DATAFILE '/path/to/undotbs1.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Change the undo retention time (reduce so that some space get released):
ALTER SYSTEM SET undo_retention = 900;
Add a new bigger undo tablespace and use that:
CREATE UNDO TABLESPACE undotbs2 DATAFILE '/path/to/undotbs2.dbf' SIZE 100M; ALTER SYSTEM SET undo_tablespace = undotbs2;
In our case, we had a big UNDO tablespace with the correct size undo retention parameter but still, our database was giving this error. Even reducing undo retention value further was not helping much.
We worked with Oracle Support and Oracle gave us two hidden parameters to apply. After changing this parameter UNDO tablespace released space and we were running well then afterward.
connect as sysdba
alter system set "_undo_autotune"=FALSE scope=BOTH SID='*';
alter system set "_smu_debug_mode"=33554432 scope=BOTH SID='*';
NOTE: Do not set these hidden parameters until Oracle confirms you in SR.
Details on hidden parameter “_undo_autotune”
The undo retention time is the amount of time that undo data is kept in the undo tablespace to support read consistency and rolling back transactions.
The “_undo_autotune” is a hidden parameter in Oracle Database that controls the automatic tuning of the undo retention time. It determines whether the undo retention time will be automatically adjusted by the database to meet the requirements of the current workload.
It should be used when the database administrator wants to manually control the undo retention time and not have it automatically adjusted.
The “_undo_autotune” hidden parameter has two possible values in Oracle Database:
FALSE (0): The undo retention time is not automatically adjusted and must be manually set by the database administrator.
TRUE (1): The undo retention time is automatically adjusted by the database to meet the requirements of the current workload. The database uses an algorithm to calculate the appropriate undo retention time based on factors such as the rate of undo generation, the rate of commit operations, and the amount of free space in the undo tablespace.
The default value for “_undo_autotune” is TRUE (1).
In some cases, the database administrator may want to manually control the undo retention time and not have it automatically adjusted. For example, if there are specific requirements or constraints that need to be met, or if the database administrator wants to fine-tune the undo retention time based on the specific needs of the workload. In such cases, the “_undo_autotune” parameter can be set to FALSE (0).
It’s important to note that setting “_undo_autotune” to FALSE (0) does not prevent the database from automatically adjusting the undo retention time in response to certain events, such as an instance crash or a low-space condition in the undo tablespace. The “_undo_autotune” parameter only controls the automatic tuning of the undo retention time in normal operating conditions.
Benefits of using “_undo_autotune” are:
- Provides greater control over the undo retention time.
- Allows for fine-tuning of the undo retention time based on specific needs and requirements.
- Prevents unexpected changes to the undo retention time due to automatic tuning by the database.
Details on _smu_debug_mode hidden parameter
The “_smu_debug_mode” is a hidden parameter in Oracle Database that enables debug mode for the System Monitoring and Usage (SMU) framework. The SMU framework is used to collect and manage performance and diagnostic data for various components of the database.
To use “_smu_debug_mode”, the parameter must be set to a value of Non-Zero (TRUE) and a support representative must be engaged. The support representative will then use the data collected in debug mode to diagnose and resolve issues.
The “_smu_debug_mode” parameter should only be used when directed by Oracle Support or when instructed by an Oracle consultant. It should not be used in normal operating conditions, as it can have a negative impact on performance and cause increased disk space usage.
Use the following command to unset the parameter:
SQL> alter system set “_smu_debug_mode” = 0;
The default value of _smu_debug_mode is 0.
We made _smu_debug_mode = 33554432
This is a workaround for Bug 5387030 – Automatic tuning of undo_retention causes unusual extra space allocation (Doc ID 5387030.8).
“_smu_debug_mode = 33554432” causes the v$undostat.tuned_undoretention to be calculated as
the maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
- Oracle Database Service for Azure (ODSA) 3 – Database Services - May 1, 2023
- Oracle Database Service for Azure (ODSA) 2 – Interconnect Details - April 30, 2023
- Oracle Database Service for Azure (ODSA) 1 – Fundamentals - April 29, 2023