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

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

Error: ORA 1628

Text: max # of extents reached for rollback segment

-------------------------------------------------------------------------------

Cause: An attempt was made to extend a rollback segment that already has

reached its maximum size or space could not be allocated in the data

dictionary to contain the definition of the object.

Action: If possible, increase the value of either the MAXEXTENTS or

PCTINCREASE initialization parameters or find the data dictionary

table lacking space and alter the storage parameters, as described in

the.

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]

Information in this document applies to any platform.

Purpose

Purpose of this document is to have a checklist for troubleshooting ORA-01628 errors i.e max # extents (32765) reached for rollback segmentwhen using Automatic Undo Management (AUM).

Troubleshooting Steps

First: Check UNDO tablespace utilization and tuned undo retention :

Aspect of the problem can be due to long running queries which can raise tuned_undoretention to very high values and exhausts the undo tablespace resulting in ORA-1628.

So before diagnosing 1628 errors, it is important first 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, Invistiagte/Resolve any excessive allocation of ACTIVE/UNEXPIRED extents and high calculation of tuned_undoretention.

Second: 1628 troubleshooting :

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 (this is also the recommended solution of Bug 10330444 and Bug 10229998 which were filed for the same issue and closed as not a bug).

To sum up:

The ORA-1628 error 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.

Suggested solutions

1) Online all available UNDO segments by setting the following parameter to distribute transactions load among more undo segments :

alter system set "_rollback_segment_count"=999999 scope=spfile;

Bounce the database.

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;

A fix to Bug:7291739 is to set a new hidden parameter, _HIGHTHRESHOLD_UNDORETENTION to set a high threshold for undo retention completely distinct from maxquerylen:

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;

c) alter rollback segment "_SYSSMU$" shrink;

d) alter system set "_smu_debug_mode" =;

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)

The steps for recreating an undo tablespace are in Note 268870.1 Ext/Pub How to Shrink the datafile of Undo Tablespace.

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.

Known issues/bugs

Bug 17306264 - ORA-1628: MAX # EXTENTS (32765) REACHED FOR ROLLBACK SEGMENT - OFTEN ENCOUNTERE

Bug 17306264 or the Patch 17306264 for 11g readme contains pre-requisite step to set the below event

event="64000 trace name context forever, level 25"

We recommend to set the event 64000 to level 25, as mentioned in the readme of the patch.

Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION (Doc ID 7291739.8)

Bug 6499872 - ORA-01628: max # extents (32765) for rollback seg (Doc ID 6499872.8)

How To Check the Usage of Active Undo Segments in AUM (Doc ID 1337335.1)

Data Pump (or other Oracle process) Reports ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$ (Doc ID 1434643.1)

ORA-1628 Max # Extents Reached Using AUM On Locally Managed Tablespace (Doc ID 761176.1)

Ora-01628: Max # Extents (32765) Reached For Rollback Segment. (Doc ID 837853.1)

ORA-1628: max # extents 32765 reached for rollback segment _SYSSMUxxx$ (Doc ID 432652.1)

Troubleshooting ORA-01555/ORA-01628/ORA-30036 during export and import (Doc ID 1579437.1)

References

BUG:7291739 - CONTENTION UNDER AUTO-TUNED UNDO RETENTION

BUG:17306264 - ORA-1628: MAX # EXTENTS (32765) REACHED FOR ROLLBACK SEGMENT - OFTEN ENCOUNTERE

NOTE:1337335.1 - How To Check the Usage of Active Undo Segments in AUM

NOTE:1434643.1 - Data Pump (or other Oracle process) Reports ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$

NOTE:7291739.8 - Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION

BUG:10330444 - AUM ORA-01628 ERROR AFTER UPGRADED TO 11.2.0.2

BUG:10229998 - ORA-01628: MAX # EXTENTS (32765) REACHED FOR ROLLBACK SEGMENT _SYSSMU34_14488782

NOTE:432652.1 - ORA-1628: max # extents 32765 reached for rollback segment _SYSSMUxxx$

NOTE:1579437.1 - Troubleshooting ORA-01555/ORA-01628/ORA-30036 during export and import

NOTE:6499872.8 - Bug 6499872 - ORA-01628: max # extents (32765) for rollback seg

NOTE:761176.1 - ORA-1628 Max # Extents Reached Using AUM On Locally Managed Tablespace

NOTE:837853.1 - Ora-01628: Max # Extents (32765) Reached For Rollback Segment.