Usage of UNDO tablespace constantly over 99%
Hi!
I have a strange situation with one of our OLTP databases and just can't find explanation for it so I've decided to explain problem here on forum.
Usage of our UNDO1 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 - UNDO1 tablespace was 2 GB large.
So to avoid ORA-01555 errors I've created another undo tablespace UNDO2 and made that tbs as default.
Then I could peacefully examine what happened.
I queried DBA_UNDO_EXTENTS and found out that there were lots of undo segments situated in UNDO1 tbs.
Then i queried:
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 UNDO1 tablespace.
How come that this UNDO1 tablespace was 100% but when I tried to find transactions using undo/rollback segments I cannot find none.
OS: SunOS
DB: 9.2.0.6.0
undo_management=AUTO
undo_retention=14400
Edited by: msutic on Feb 11, 2009 4:12 PM