Usage of UNDO tablespace constantly over 99% ?!

We had strange situation with one of our OLTP databases and I've spent some time to find explanation for it but still no answer that satisfies me.
When I find acceptable answer or Metalink document about this problem I'll post info here just for reminder.


Info:
OS: SunOS
DB: Oracle 9.2.0.6.0

undo_management=AUTO
undo_retention=14400
undo-size = 2GB

Usage of our UNDOTBS1 tablespace raised to 100% and I've decided to free some undo space because ORA-01555 errors started to show up.



To free some space I've tried to find transactions that were using undo blocks and commit or rollback those transactions.

But when I queried:

select t.used_ublk, t.*
from v$transaction t
order by t.used_ublk desc;

there were transactions with just few blocks used - UNDOTBS1 tablespace was 2 GB large.

So to avoid ORA-01555 errors I've created another undo tablespace UNDOTBS2 and made that tbs as default.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/ora/oradata/undotbs02.dbf' SIZE 2048M REUSE AUTOEXTEND OFF;

alter system set undo_tablespace=UNDOTBS2 scope=both;

Then I could without pressure examine what happened.
I queried DBA_UNDO_EXTENTS and found out that there were lots of undo segments situated in UNDOTBS1 tablespace.

select segment_name, owner, tablespace_name, status
from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

SYSTEM SYS SYSTEM ONLINE
_SYSSMU1$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU10$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU11$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU12$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU13$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU14$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU15$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU16$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU17$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU18$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU19$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU20$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU21$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU24$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU27$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU30$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU31$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU32$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU33$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU34$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU35$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU36$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU37$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU38$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU39$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU40$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU41$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU42$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU43$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU44$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU45$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU46$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU47$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU48$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU49$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU50$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU51$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU52$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU53$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU54$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU55$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU56$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU57$ PUBLIC UNDOTBS2 ONLINE

Only offline rollback segments in UNDOTBS1 tablespace.

How come that this UNDOTBS1 tablespace was used 100% but when I tried to find transactions which were using that undo/rollback segments I couldn't find none.

I understand that there was transaction/transactions that eventually used whole UNDO tablespace, but they finished with their work - commit or rollback was executed.

This allocated UNDO space should be reused by another transactions - but insted of reusing UNDO space I got several:

ORA-01555 caused by SQL statement below (Query Duration=78 sec, SCN: 0x0004.f253ce76):

This was production database so I recreated UNDO tablespace as it was before. I've dropped UNDOTBS1 and created new one on the same location. Also made id as a default undo tablespace. After that dropped newly created UNDOTBS2.

I think that this was Oracle bug but still I am eager to find metalink document that confirms my thoughts.



Link on OTN forum: Click here!