UNDO Tablespace Full

From: ADRoman <alonso.duarte_at_gmail.com>
Date: 19 Jun 2006 15:08:03 -0700
Message-ID: <1150754883.710788.71670@c74g2000cwc.googlegroups.com>

Hi all;

I have AUM as part of undo schema in my data base. I have 2 UNDO Tablespaces, and I switch them according to the workload. Problem is that one of them is full a day ago. I executed the following query to check the undo segments with allocated space.

select SEGMENT_NAME, BYTES/1024/1024 "MB" , BLOCKS, EXTENTS, TABLESPACE_NAME
from dba_segments where SEGMENT_TYPE in ('ROLLBACK','TYPE2 UNDO') ORDER BY EXTENTS DESC This is the output;

SEGMENT_NAME    MB        BLOCKS  EXTENTS   TABLESPACE_NAME


_SYSSMU37$      9860.3125  1262120   808             UNDOTBS2

_SYSSMU8$    2102.679688   269143    603             UNDOTBS1

_SYSSMU22$  255.2421875     32671    198             UNDOTBS2

_SYSSMU44$  232.9296875     29815      64             UNDOTBS1

_SYSSMU21$    22.9296875       2935     52              UNDOTBS2

The above list are my undo segments for every tablespace. The UNDOTBS2 tablespace is full.
I use the following query to check what segments are being used by users;

SELECT r.name "RB NAME ", p.pid "ORACLE PID", p.spid "SYSTEM PID ", NVL (p.username, 'NO TRANSACTION') "OS USER", s.UserName, s.Status,

p.terminal FROM v$lock l, v$process p, v$rollname r, v$session s WHERE l.sid = s.sid(+) AND s.paddr = p.addr AND

     TRUNC (l.id1(+)/65536) = r.usn AND
      l.type(+) = 'TX' AND
      l.lmode(+) = 6 ORDER BY r.name;

The output shows that NONE of the full segments (from the first Query), are being used by any user (except segment _SYSSMU22$)

RB NAME ORACLE PID SYSTEM_PID OS USER USERNAME STATUS TERMINAL
_SYSSMU10$ 275 24540 oracle
KNAVARRE INACTIVE UNKNOWN
_SYSSMU21$ 179 11339 oracle

FRAMOS     ACTIVE  UNKNOWN


_SYSSMU22$         304               26756           oracle

HCASTANO INACTIVE  UNKNOWN


_SYSSMU23$         100               23326           oracle

  SDIAZ  INACTIVE  UNKNOWN


_SYSSMU25$         192               21125           oracle

MSALAZARP  INACTIVE  UNKNOWN


_SYSSMU7$           114               16474           oracle

CFIGUERO INACTIVE UNKNOWN Can anyone tell me why the full segments are not being released, if nobody are using them??
It has been 24 hours and they are still full.

Thanks in advance.

Alonso Duarte Received on Mon Jun 19 2006 - 17:08:03 CDT