Undo

Query Undo Segments 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 set pagesize 1000 set linesize 200 select tablespace_name, segment_name, round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES", round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES", round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES", NO_OF_EXTENTS from ( select tablespace_name, segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS from DBA_UNDO_EXTENTS where status='ACTIVE' and tablespace_name like 'UNDOTBS%' group by tablespace_name, segment_name union select tablespace_name, segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS from DBA_UNDO_EXTENTS where status='UNEXPIRED' and tablespace_name like 'UNDOTBS%' group by tablespace_name, segment_name union select tablespace_name, segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS from DBA_UNDO_EXTENTS where status='EXPIRED' and tablespace_name like 'UNDOTBS%' group by tablespace_name, segment_name ) group by tablespace_name, segment_name, NO_OF_EXTENTS having NO_OF_EXTENTS >= 30 order by 5 desc; Example Query Output