ORA-01628: max # extents (32765) reached for rollback segment

Error: ORA-01628: max # extents (32765) reached for rollback segment is occurring in a transaction that is generating a lot of undo data, during an add extent operation in an undo segment and is indicating we have hit the MAXEXTENTS (32765) and then we cannot extend the undo segment.

Here is troubleshooting steps which can be do when ORA-01628: max # extents (32765) reached for rollback segment error occurs in your database.
(Doc ID 1580182.1)

1)
It is important to check UNDO tablespace utilization and tuned undo retention as follow :

SQL> SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;

SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

SQL> select sum(bytes) from dba_free_space where tablespace_name='&UNDOTBS';

Before proceed, Invistigate/Resolve any excessive allocation of ACTIVE/UNEXPIRED extents and high calculation of tuned_undoretention.

2)
Basically, It is obvious to see high undo usage when there are huge transactions.

Here is a query you can use to find out how much undo a transaction is using:

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr;

As you know, with automatic undo, you have no control over the extent sizes, nor do you have the ability to shrink them. This all happens automatically.
The system automatically decides on the extent size, however it will generally start allocating larger extents if an undo segment is extending a lot.
The large number of extents in the rollback segment is likely due to fragmentation in the undo tablespace:
Oracle can probably only allocate extents of 64k due to fragmentation, so it is very probable to hit the max extents issue.

The maximum number of extents for undo segments is limited to 32K and a long/large runing transaction can exhaust this limit by adding
new extents if the next extent of the current one is not an expired one and finally will receive ORA-1628.

So, after getting the ORA-1628 errors in the transaction that extended the undo segment until its limit,
future transactions will not be allowed to bind to the undo segment until it is not shrinked (you may see that the number of extents is decreased).

So, The two major causes of ORA-1628 issue are a very large transaction or undo tablespace fragmentation.
In case of large transaction, That is solved by splitting the large transaction to smaller ones (e.g. frequent commits).

In case of undo tablespace fragmentation, That is solved by recreating the undo tablespace.

Solutions :

1)
Set parameter "_rollback_segment_count" to online more available UNDO segments. Value should be set by placing the highest value obtained of the following queries:

select status,count(*) from dba_rollback_segs group by status; --You add OFFLINE+ONLINE to get the number

select max(maxconcurrency) from wrh$_undostat;

select max(maxconcurrency) from v$undostat;

2)
In case you have large value for TUNED_UNDORETENTION :

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;

SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

ALTER SYSTEM SET "_highthreshold_undoretention"=max(maxquerylen)+1;

3)
Before/after running large transactions, Shrink undo segments when reaching certain threshold (Ex: 15000 extents) do not wait to reach its maximum (32765) to be able to bring it below certain threshold so that this undo segment can qualify for binding again.

a) select a.inst_id,
       a.ksppinm  "Parameter",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value"
  from x$ksppi a, x$ksppcv b, x$ksppsv c
 where a.indx = b.indx
   and a.indx = c.indx
   and a.inst_id = b.inst_id
   and b.inst_id = c.inst_id
   and a.ksppinm in ('_smu_debug_mode')
 order by 2;


select segment_name,
       round(nvl(sum(act), 0) / (1024 * 1024 * 1024), 3) "ACT GB BYTES",
       round(nvl(sum(unexp), 0) / (1024 * 1024 * 1024), 3) "UNEXP GB BYTES",
       round(nvl(sum(exp), 0) / (1024 * 1024 * 1024), 3) "EXP GB BYTES",
       NO_OF_EXTENTS
  from (select segment_name,
               nvl(sum(bytes), 0) act,
               00 unexp,
               00 exp,
               count(*) NO_OF_EXTENTS
          from DBA_UNDO_EXTENTS
         where status = 'ACTIVE'
           and tablespace_name = 'UNDOTBS1'
         group by segment_name
        union
        select segment_name,
               00 act,
               nvl(sum(bytes), 0) unexp,
               00 exp,
               count(*) NO_OF_EXTENTS
          from DBA_UNDO_EXTENTS
         where status = 'UNEXPIRED'
           and tablespace_name = 'UNDOTBS1'
         group by segment_name
        union
        select segment_name,
               00 act,
               00 unexp,
               nvl(sum(bytes), 0) exp,
               count(*) NO_OF_EXTENTS
          from DBA_UNDO_EXTENTS
         where status = 'EXPIRED'
           and tablespace_name = 'UNDOTBS1'
         group by segment_name)
 group by segment_name, NO_OF_EXTENTS
 order by 5 desc;


select sum(blocks), count(*) extents, segment_name
  from DBA_EXTENTS
 where tablespace_name = 'UNDOTBS1'
 group by segment_name
 order by 2 desc;

b) alter system set "_smu_debug_mode" = 4 scope=memory;

c) alter rollback segment "_SYSSMU$" shrink;

d) alter system set "_smu_debug_mode" =  scope=memory;

Then you can check the result of this measure by running the query in step a again before and after the above three steps.

4) Drop and recreate undo tablespace (due to it's fragmentation)

5) Minimize the generated undo as much as possible :

Example:

- split large transactions into smaller one

- commit more often

- use direct path load rather than conventional path load to significantly reduce the amount of undo and thus also avoid a too high fragmentation of undo tablespace.