ORA-30036: unable to extend segment by 8 in undo tablespace 'APPS_UNDOTS1'

[satish]

Hi Erman,

We are using 19.28 oracle database version.


We are purging stats using the below procedure as part of applying timezone upgrade.

SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
COUNT(*)
23585522

select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
COUNT(*)
32907064


SQL> exec DBMS_STATS.PURGE_STATS(systimestamp);
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'APPS_UNDOTS1'
ORA-06512: at "SYS.DBMS_STATS", line 47863
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 193
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 12212
ORA-06512: at "SYS.DBMS_STATS", line 47850
ORA-06512: at "SYS.DBMS_STATS", line 47876
ORA-06512: at line 1


SQL>select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
COUNT(*)
     0

SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
COUNT(*)
0


Status   |Name                     |Size (GB)     |Used (GB)      |Free (GB)      |(Used) %
---------|-------------------------|--------------|---------------|---------------|---------------
ONLINE   |APPS_UNDOTS1             |          8.61|        8.57   |          0.04 |  99.53


Alert Log:
SUPPDB(4):about to recover undo segment 47 status:6 inst:2
2026-01-28T08:01:13.195940+05:30
SUPPDB(4):about to recover undo segment 47 status:6 inst:2
2026-01-28T08:06:13.207889+05:30
SUPPDB(4):about to recover undo segment 47 status:6 inst:2
2026-01-28T08:08:58.067147+05:30
Incremental checkpoint up to RBA [0xa6e.160a3.0], current log tail at RBA [0xa6e.1773c.0]
2026-01-28T08:11:13.211856+05:30
SUPPDB(4):about to recover undo segment 47 status:6 inst:2
2026-01-28T08:16:13.235866+05:30
SUPPDB(4):about to recover undo segment 47 status:6 inst:2
2026-01-28T08:16:43.781574+05:30


SQL> sho pdbs
CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     4 SUPPDB                         READ WRITE NO

SQL> select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where tablespace_name='APPS_UNDOTS1' and status ='NEEDS RECOVERY';
no rows selected


Will the space get reused by other sessions as the tablespace query shows 99% usage of undo?

Do we need to take any action here? even though exec DBMS_STATS.PURGE_STATS(systimestamp); errored out, the records in the table are purged as shown above.

Thanks,
SG

[ErmanArslansOracleBlog]

a successful failure scenario..

Probably purge work is finished. I mean the heavy part. It seems like procedure got that error in the last stage(s), maybe while updating metadata or something like that.
Oracle does the recover itself. The things that you see in the alert log is an evidence of that.
That undo will be reused, it will be overwritten when there is a pressure there ( if there is no retention guarantee configured)

So what I would do is;

Wait for Alert Log to go quiet, and check your undo is healthy -- active,unexpired and expired percanteges of it.
Confirm there are no active rollbacks remaining.
Ensure ACTIVE undo extents have dropped and moved to UNEXPIRED or EXPIRED.
Run a "Test" Purge -> run exec DBMS_STATS.PURGE_STATS again.