ADRoman wrote:
> 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
Read the concepts books on UNDO at http://tahiti.oracle.com.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jun 19 2006 - 17:57:03 CDT
