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