Re: UNDO Tablespace Full

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
>

Daniel is correct. You need to read the concepts manual to understand AUM.

In brief - there is no need for multiple undo tablespaces. One will do. The other is just wasted disk space. Properly size of of them and get rid of the other.

The A in AUM stands for automatic. Space within the undo tablespace will be managed *automatically* and you don't need to worry about it. For a given committed transaction, the space will be released as soon as it's older than the undo_retention setting. It can even be reused before that if the transaction is committed and a current transaction needs undo space and can't find anything free. At that point Oracle will start grabbing undo space from the oldest committed transactions, but that increases the risk of encountering ora-1555's.

The only thing you need to worry about with AUM is setting it up properly from the start. Set the undo_retention period to the number of seconds required by longest running query, and set the tablespace size large enough to hold all the undo generated in that amount of time. The OEM undo advisor can help with that. Received on Tue Jun 20 2006 - 09:21:41 CDT