[Quick Tips] ORA-30036: unable to extend segment by 8 in undo

[Legacy User]

The error:

U0003590 DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-30036: unable to extend segment by 8 in undo

This error occurs in the Database, the database is not able to allocate more memory to the segment in tablespace 'UNDOTBS1''. Upon noticing this message please URGENTLY get in contact with your database administrator to extend the tablespace to prevent this error from happening.

[Legacy User]

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

Cause: the specified undo tablespace has no more space available.

Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

It is important to note that ORA-30036 is associated with UNDO tablespaces.  If you use autoextend on an UNDO tablespace  because you can easily run out of UNDO and receive ORA-30036 along with an aborted update.

[Legacy User]

I've found a few good articles with regards to the UNDO tablespace, sizing and handling the error ORA-30036

UNDO Tablespace sizing: 
http://www.dba-oracle.com/t_undo_tablespace_autoextend.htm 

Managing and Sizing the UNDO Tablespace: 
https://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm 

There are a few tricks to get around particularly large transactions which utilize the UNDO tablespace if you cannot make room on the DB server to expand it. The first article explains a workaround which essentially eliminates the rollback option by going around the UNDO tablespace (not recommended as you will not be able to rollback the transaction). I hope this helps!

[Legacy User]

Finally this is a task for an DBA to monitor the database.
Also to do an 'database health check' periodically to avoid a situation such like this (analysis of AWR or ADDM reports).

[Olgun Onur Ozmen]

Hi Comm,

After this problem, it was automatically turned off the many agents in a 3-4 minute down by making an automatic rollback itself and automatically turned it normal work again. Its countinued to normal operation. After 5-6 hours, we notice it from old alert and dba enlarged the tablespace. The 3-minute interruption is not a big deal, but we wondered if it would happen again in the future.

version: 21.0.8+hf.1.build.1695196809414

Support cannot give any clear information says "that it may be caused by any operation in the database". Has anyone experienced it? Does anyone have any information? 

20231230/012122.726 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: 20d3ad0  DB-HDBC: 214e140
20231230/012122.726 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20231230/012122.726 - U00003592 UCUDB - Status: '' Native error: '30036' Msg: 'ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1''
20231230/012122.726 - U00003536 UCUDB: FATAL DATA BASE ERROR: Re-connection will be attempted in 10 seconds.
20231230/012123.172 - U00003538 UCUDB: Re-connection to database successful. Processing will continue.
20231230/012125.173 - U00000006 DEADLOCK or Connection to database lost - Rollback handling initiated. See previous messages.

Thank You.

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------