Oracle Undo

 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;

1
2
3
4
5
6
7
8
9

TABLESPACE_NAME                SEGMENT_NAME                   ACT GB BYTES UNEXP GB BYTES EXP GB BYTES NO_OF_EXTENTS
------------------------------ ------------------------------ ------------ -------------- ------------ -------------
UNDOTBS1                       _SYSSMU2051_2474202737$                   0              0         6.22         32493
UNDOTBS1                       _SYSSMU38_1430028107$                     0              0        4.822         23885
UNDOTBS1                       _SYSSMU40_1024617793$                     0              0        3.282         32765
UNDOTBS1                       _SYSSMU66_3336917028$                     0              0        1.485         11699
UNDOTBS1                       _SYSSMU33_1487053986$                     0              0        1.158         10194
UNDOTBS1                       _SYSSMU39_1578867636$                     0              0         .886          5388
UNDOTBS1                       _SYSSMU70_2249094061$                     0              0         .882          7954