Oracle Database Discussions

Oracle Database --- 9.2.0.5

OS -- HPUX

We have one database where undo tablespace size is already 50 GB. And it appears it is growing quite rapidly.

When we query DBA_UNDO_EXTENTS we don't see much EXPIRED extents. EXPIRED extents only amount to about 10 MB in Undo tablespace of 50 GB.
Most are still ACTIVE or UNEXPIRED. So I allocated some more space. But even that is rapidly being used up.

I see only three user session are ACTIVE. and one of them seem to be generating lots of Undo.

I used the following query,


select s.username, s.sid, t.status, t.used_ublk, t.used_urec,
decode(bitand(t.flag,128),0,'NO','YES') rolling_back from v$session s,
v$transaction t where s.taddr=t.addr;

USERNAME SID STATUS USED_UBLK USED_UREC ROL
------------------------------ ---------- ---------------- ---------------- ---------- ---------- ---
AVC_SA 56 ACTIVE 1 8 NO
RXB_SA 188 ACTIVE 9024 327891 NO
PERFSTAT 140 INACTIVE 0 0 YES

How to overcome this problem ? We cannot allocate more and more space to this Undo tablespace. Filesystem is also running out of space.