Hello,
Few days ago I noticed that my undo tablespace has almost 30GB. Most of them is active extents.
"select count(segment_name),sum(bytes/1024/1024),status from dba_undo_extents group by status;"
COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS
------------------- -------------------- ---------
37 52.8125 UNEXPIRED
35 23.25 EXPIRED
1715 28342.5625 ACTIVE
There is no active transaction: v$transaction is empty.
Query: "select owner,segment_name,SEGMENT_TYPE, blocks,FREELISTS from dba_segments WHERE tablespace_name = 'UNDOTBS1';" shows below information:
| OWNER | SEGMENT_NAME | SEGMENT_TYPE | BLOCKS FREELISTS |
------------------------------ --------------------------------------------------------------------------------- ------------------ ---------- ----------
| SYS | _SYSSMU18_1849707872$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU17_3141271538$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU16_2494449141$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU15_4263200518$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU14_2843354259$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU13_408012776$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU12_343100992$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU11_4136977176$ | TYPE2 UNDO | 16 | 1 |
| SYS | _SYSSMU10_1197734989$ | TYPE2 UNDO | 1168 | 1 |
| SYS | _SYSSMU9_1650507775$ | TYPE2 UNDO | 1424 | 1 |
| SYS | _SYSSMU8_517538920$ | TYPE2 UNDO | 656 | 1 |
| SYS | _SYSSMU7_2070203016$ | TYPE2 UNDO | 1424 | 1 |
| SYS | _SYSSMU6_1263032392$ | TYPE2 UNDO | 1424 | 1 |
| SYS | _SYSSMU5_898567397$ | TYPE2 UNDO | 1881880 | 1 |
| SYS | _SYSSMU4_1254879796$ | TYPE2 UNDO | 656 | 1 |
| SYS | _SYSSMU3_1723003836$ | TYPE2 UNDO | 1304 | 1 |
| SYS | _SYSSMU2_2996391332$ | TYPE2 UNDO | 1424 | 1 |
| SYS | _SYSSMU1_3724004606$ | TYPE2 UNDO | 1745968 | 1 |
As you can see two segments (_SYSSMU5_898567397$ and _SYSSMU1_3724004606$ ) are extremely big. Why?
Dump header of "_SYSSMU5_898567397$" in "TRN TBL" section indicates state = 9 (which means "transaction is committed").
Are these "active" extents really active? Is it normal state?
With best regards
Jacek