我好像是遇到IF: ORA-1628 Reported During Long Running Transactions (文档 ID 1951032.1) 这个bug了？

IF: ORA-1628 Reported During Long Running Transactions (文档 ID 1951032.1)

In this Document

Symptoms

Cause

Solution

Related Issues:

References

---

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2017***

SYMPTOMS

ORA-01628 errors i.e max # extents (32765) reached for rollback segment <SEGMENT_NAME>

e.g

ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$

CAUSE

ORA-1628 is reported when the number of extents in any particular Undo segment reach 32k. This can happen when many small size extents are allocated (Undo extents are not uniform sized. It follows Autoallocate algorithm). Smaller extents are allocated to Undo segments when there is a space constraint. Undo fragmentation, high tuned_retention are the other possible causes.

SOLUTION

1) First step will be to the tune the failing query. Split the Large/Long transactions into smaller ones like e.g include frequent commits etc..

2) Check if many 8K (or small size) extents are allocated to the Undo segment.

SELECT segment_name, bytes "Extent_Size", count(extent_id) "Extent_Count", bytes * count(extent_id) "Extent_Bytes" FROM dba_undo_extents WHERE status = 'ACTIVE' group by segment_name, bytes order by 1, 3 desc;

If many small size extents are allocated, apply Patch 17306264. And set 

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

The patch is fixed in 12.1 and above.

The event in patch controls allocation of extents to each undo segment. Once the 8k of extents are allocated to any undo segment , next extent allocation would look for extent of size 1M or its multiple in free space in undo tablespace to allocate to the same segment where extension is required.

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

SQL> alter system set "_rollback_segment_count"=1000 scope=spfile;

  Bounce the database.

The _rollback_segment_count parameter should be set to a value higher than the concurrent sessions/transactions in the database.

4) Create a new undo tablespace with a larger size: 

SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size <new size>;

 Set the new tablespace as the undo tablespace to be used: 

SQL> alter system set undo_tablespace=undo_rbs1;

Note: Do not drop the old undo tablespace immediately

5) Shrink the undo segment shown in the error

e.g consider _SYSSMU4$ is the rollback segment shown in above ORA-1628 error, then

SQL> alter system set "_smu_debug_mode" = 4;

SQL> alter rollback segment "_SYSSMU4$" shrink; (based on the above example)

SQL> alter system set "_smu_debug_mode" = 0;

 

For more details refer to the troubleshooting guide:

 

Troubleshooting ORA-1628 - max # extents (32765) reached for rollback segment <SEGMENT_NAME> (Doc ID 1580182.1)

Related Issues:

ORA-1628 can be reported for small transactions too. This can happen when the undo tablespace is fragmented or there is space crunch in the undo tablespace. High value of TUNED_UNDORETENTION also cause ORA-1628 error. Refer to Doc ID 1951033.1 for details.

ORA-1628 error during Import can be resolved by setting some IMPDP parameters. Refer to Doc ID 1955310.1 for details.