Error
Error Code: ORA-30036

Oracle ORA-30036: Undo Tablespace Full

📦 Oracle Database
📋

Description

The ORA-30036 error indicates that the undo tablespace in your Oracle database has run out of space. This occurs when there's insufficient space to store undo information for active transactions, preventing further data modifications.
💬

Error Message

ORA-30036: unable to extend segment by string in undo tablespace ' string '
🔍

Known Causes

4 known causes
⚠️
Insufficient Tablespace Size
The allocated size for the undo tablespace is simply too small to handle the volume of undo data generated by ongoing transactions.
⚠️
Long-Running Transactions
A single or multiple long-running transactions are consuming a significant portion of the undo tablespace, preventing other operations from allocating space.
⚠️
High Transaction Volume
A sudden surge in transaction activity can quickly exhaust the available space in the undo tablespace.
⚠️
Inadequate Undo Retention
The UNDO_RETENTION parameter is set too high, retaining undo data for an extended period and preventing it from being overwritten.
🛠️

Solutions

4 solutions available

1. Temporarily Increase Undo Tablespace Size easy

Quickly add space to the undo tablespace to resolve the immediate issue.

1
Identify the name of the undo tablespace from the ORA-30036 error message. Let's assume it's 'UNDOTBS1'.
2
Connect to your Oracle database as a user with DBA privileges (e.g., SYSDBA).
3
Add a new data file to the undo tablespace. Choose a reasonable size, e.g., 1GB (1024MB).
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/path/to/your/oracle/datafiles/undotbs1_02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
4
Alternatively, if the existing data file is set to autoextend, you can increase its MAXSIZE.
ALTER DATABASE DATAFILE '/path/to/your/oracle/datafiles/undotbs1_01.dbf' RESIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
5
Verify the undo tablespace size and autoextend settings.
SELECT file_name, tablespace_name, bytes/1024/1024 MB, autoextensible, Increment_by, Maxbytes/1024/1024 MAX_MB FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1';

2. Extend Existing Undo Datafile easy

Resize an existing autoextensible undo datafile to accommodate growth.

1
Identify the name of the undo tablespace and the specific data file that is causing the issue. The error message often implies the segment that failed to extend.
2
Connect to your Oracle database as a user with DBA privileges.
3
Determine the current size and autoextend settings of the relevant data file.
SELECT file_name, bytes/1024/1024 MB, autoextensible, Increment_by, Maxbytes/1024/1024 MAX_MB FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1' AND file_name LIKE '%undotbs1_01%';
4
If the data file is autoextensible and has not reached its MAXSIZE, you can either increase MAXSIZE or explicitly resize it.
ALTER DATABASE DATAFILE '/path/to/your/oracle/datafiles/undotbs1_01.dbf' RESIZE 5120M;
5
If the MAXSIZE was already unlimited or very large, and the file still couldn't extend, it might be due to disk space limitations on the operating system. Check OS disk space.

3. Configure Undo Retention and Autoextend medium

Optimize undo retention and ensure proper autoextend settings for long-term stability.

1
Understand your application's undo requirements. Long-running queries or large transactions require more undo space and longer retention.
2
Connect to your Oracle database as a user with DBA privileges.
3
Check the current UNDO_RETENTION parameter. This determines how long undo data is kept. A common default is 900 seconds (15 minutes).
SHOW PARAMETER undo_retention;
4
Increase UNDO_RETENTION if you have long-running queries that might need to read older undo data. For example, to 3600 seconds (1 hour):
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;
5
Ensure your undo tablespace datafiles are configured with AUTOEXTEND ON and a reasonable MAXSIZE. If MAXSIZE is too small, it can lead to this error.
SELECT file_name, tablespace_name, autoextensible, Increment_by, Maxbytes/1024/1024 MAX_MB FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1';
6
If MAXSIZE is too small, increase it. For example, to 10GB:
ALTER DATABASE DATAFILE '/path/to/your/oracle/datafiles/undotbs1_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10240M;
7
Consider setting the undo tablespace to BIGFILE if you anticipate very large amounts of undo data. This allows for a single, very large datafile.
CREATE BIGFILE TABLESPACE UNDOTBS1 DATAFILE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

4. Analyze and Optimize Transactions advanced

Identify and resolve specific transactions or application logic causing excessive undo generation.

1
This is a more complex solution and requires deep understanding of your application and database activity.
2
Connect to your Oracle database as a user with DBA privileges.
3
Identify active transactions that are generating significant undo. You can use V$TRANSACTION and V$SESSION.
SELECT s.sid, s.serial#, s.username, s.program, t.start_time, t.used_ublk, t.used_urec FROM v$transaction t JOIN v$session s ON t.addr = s.taddr ORDER BY t.used_ublk DESC;
4
If a specific session is consuming a large amount of undo, investigate its activity. You might need to use SQL Trace or Extended SQL Tracing (SQL_TRACE=TRUE or DBMS_MONITOR).
5
Look for long-running DML operations (INSERT, UPDATE, DELETE) that are modifying large amounts of data. Optimize these statements or break them into smaller batches.
6
Review application logic. Are there unnecessary updates or complex operations that generate excessive undo? Can they be simplified or made more efficient?
7
Consider setting session-level UNDO_RETENTION if certain critical long-running queries need guaranteed undo space, though this should be used cautiously.
ALTER SESSION SET undo_retention = 7200;
8
If the problem is intermittent, monitor the undo tablespace usage over time to identify patterns and peak usage periods.
SELECT tablespace_name, SUM(bytes)/1024/1024 MB FROM dba_free_space WHERE tablespace_name = 'UNDOTBS1' GROUP BY tablespace_name;