undo tablespace not releasing expired blocks
Hi,
It is in staging environment, RAC DB with RDBMS on 10.2.0.4 & clusterware on 11.2.0.2
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select sum(bytes)/1024/1024/1024 Gb, status from dba_undo_extents group by status;
GB STATUS
---------- ---------
17.3599243 EXPIRED
21.9983521 UNEXPIRED
2.12304688 ACTIVE
I notice there is around 17GB of expired undo in undo tablespaces.
SQL> sho parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
auto_extension is set to off & we got the error ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS2'
My understanding of undospace management is, if there is expired undo space it should be utilized, if there is transaction demanding undospace if it can't extend & doen'st have free undo blocks, it can overwrite the expired undo blocks, this error seems to indicate, it isn't doing so, any suggestions? Appreciate your valuable inputs.