Tag: v$session

UNDO Tablespace Is Full

Database alert log has many errors like this :

ORA-30036: unable to extend segment by 8 
                                    in undo tablespace 'UNDOTBS2'

1.) Check UNDO tablespace extents.

SQL> select status, count(*) nb, sum(blocks) blocks 
     from dba_undo_extents 
     where TABLESPACE_NAME='UNDOTBS2' 
     group by status;

STATUS     NB         BLOCKS
---------  ---------- ----------
UNEXPIRED  383           3064
EXPIRED    365           2920
ACTIVE    8034        8382352

2) There is no user session consuming big undo space, but all SYS user only uses 1 undo block per session.

SQL> select s.sid,s.username,t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;

SID        USERNAME    USED_UBLK
---------- ----------  ----------
 2601      SYS          1
 2835      SYS          1
 2838      SYS          1
 3072      SYS          1
 3075      SYS          1
 3311      SYS          1
 3544      SYS          1
...
..
.

3) Show which sessions used most undo space so far. Here SID=1 is MMON background process.

SQL>select s.sid, t.name, s.value
    from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'
order by s.value desc; 

SID    NAME                      VALUE
------ ------------------------- ----------
1      undo change vector size   2256552752
3308   undo change vector size   1360455432
6609   undo change vector size    157619916
2604   undo change vector size     10521640
712    undo change vector size       322840
1656   undo change vector size       302768
1892   undo change vector size       302436
1184   undo change vector size       250904
..
..
.

4) Since there is no app user sessions consuming big UNDO space, the undo space should be consumed by database background processes, and they are not recorded in v$transaction view.

Bounced the instance,  used undo space is released.

SQL> select status, count(*) nb, sum(blocks) blocks
     from dba_undo_extents
     where TABLESPACE_NAME='UNDOTBS2'
     group by status;

STATUS        NB         BLOCKS
------------ ---------- ----------
UNEXPIRED    8462       8385392
EXPIRED      368        2944

After another 15 minutes.

 SQL>select status, count(*) nb, sum(blocks) blocks
     from dba_undo_extents
     where TABLESPACE_NAME='UNDOTBS2'
     group by status;

STATUS    NB         BLOCKS
--------- ---------- ----------
UNEXPIRED 405        20520
EXPIRED   461         7280