ORA-1628: max # extents 32765 in 10g

Hi,

sun solaris 64 bit
db - 10.2.0.2

I'm getting ora-1628 errors on a undo tablespace.
ORA-1628: max # extents 32765 reached for rollback segment _SYSSMU261$

The tablespace is 200gb in size and with over 35gb of free space. The undo retention was 15 hrs.. i've reduced to 12 hrs ..but still that does not seem to help.

Many transactions are failing and the alert log is getting flooded with ora-1628 errors on the same rollback segment.

Below are the results from dba_extents and dba_ssegments.

Appreciate your help

select sum(blocks),count(*),segment_name from DBA_EXTENTS where tablespace_name = 'PSAPUNDO' group by segment_name order by segment_name;

SUM(BLOCKS) COUNT(*) SEGMENT_NAME
.
.
.
1859952 32765 _SYSSMU261$
.
.

select sum(blocks),sum(extents),segment_name from DBA_SEGMENTS
where tablespace_name = 'PSAPUNDO' group by segment_name order by segment_name;
SUM(BLOCKS) SUM(EXTENTS) SEGMENT_NAME
.
.
1859952 32765 _SYSSMU261$
.
.

Regard
Jay

Allocate more rollback segments or If possible, increase the value of
either the MAXEXTENTS or PCTINCREASE initialization parameters

You can chang the maxextents via:

alter rollback segment storage ( maxextents N );


if you are going to change next, you could:

alter rollback segment storage ( next );

Quote:

---

Originally Posted by gopu_g

Allocate more rollback segments or If possible, increase the value of
either the MAXEXTENTS or PCTINCREASE initialization parameters

You can chang the maxextents via:

alter rollback segment storage ( maxextents N );


if you are going to change next, you could:

alter rollback segment storage ( next );

---

really - think about answers before you write them - none of that is possible in automatic undo

Hi Nugpot,

Can you post the UNDO tablespace creation statement?

Agree with Dave. In 10g UNDO are automatic. Are u using any retention guarantee parameter set for UNDO.

It seems to be an oracle bug 6499872. There is no patch for 10.2.0.2., so I ended up recreating the undo tablespace.

The syntax for the undo tablespace creation was ...
CREATE UNDO TABLESPACE PSAPUNDO DATAFILE
'/oracle/XXX/YYYY/undo_23/undo.data23' SIZE 8190M AUTOEXTEND OFF,
.....
.....
......
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

Thanks
Jay