UNDO SQL

Below is some sql I used to get an idea about how undo is being managed in an oracle database.

Firstly some simple stuff ...

Look at the segments in the UNDO tablespace.

select segment_name, sum(bytes)/(1024*1024)
from dba_extents
where tablespace_name = 'tablespace_name'
group by segment_name
order by segment_name;

LOOK at current rollback segments, by status, either ONLINE or OFFLINE.

select status, count(*) from dba_rollback_segs
where owner = 'PUBLIC' group by status;

MERGE the above two statements together ... This can take a minute or so to run, but is quite useful as it shows the total number of segments that can be used. Bearing in mind that the database will try and add some more.

select a.segment_name, b.status, sum(a.bytes)/(1024*1024)
from dba_extents a, dba_rollback_segs b
where a.segment_name = b.segment_name AND b.owner = 'PUBLIC'
group by a.segment_name, b.status;