ORA-30036 and no free UNDO space

We have a database with a large amount (10Gb) of redo that has never need to be increased since the database was built. One morning we find lots of sessions suspended because someone has taken all of the undo.

Firstly, I identified the session that was causing the problem.

SELECT a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC;

The result of this query showed one huge amount of undo held by a single session. In my case it was SID 177. So next I display details about the session.

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and sid = 177;

I've not only displayed the sid and serial# I need to kill the session but also the SPID so I can kill it from unix if necessary.

I also located the SQL for that session and found it was a massive DELETE statement with no commits.

Having got agreement from the user that I could end the session, I killed it.

alter system kill session '177,13989' immediate;


Once the session was killed, I increased the size of the tablespace to allow some sessions to run while the roll back occurred.

This statement shows how much undo is still being used.

select status,
round(sum_bytes / (1024*1024), 0) as MB,
round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
select status, sum(bytes) sum_bytes
from dba_undo_extents
group by status
),
(
select sum(a.bytes) undo_size
from dba_tablespaces c
join v$tablespace b on b.name = c.tablespace_name
join v$datafile a on a.ts# = b.ts#
where c.contents = 'UNDO'
and c.status = 'ONLINE'
);

STATUS MB PERC
--------- ---------- ----------
ACTIVE 10276 96
EXPIRED 1 0
UNEXPIRED 396 4

I continued to monitor the recovery of the database thus.

select * from v$fast_start_transactions;

Note: UNDOBLOCKSDONE increases up to a fixed batch which is then removed from UNDOBLOCKSTOTAL.

Once UNDOBLOCKSTOTAL reached 0, the available bocks in undo went back to normal.

Once the recovery has completed, the above query returns this result.

STATUS MB PERC
--------- ---------- ----------
ACTIVE 81 1
EXPIRED 2 0
UNEXPIRED 10590 99

The quantity of unexpired block is now ok.




HappyJohn.