Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

many active undo extents

Jacek ZOct 6 2016 — edited Oct 6 2016

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 3 2016
Added on Oct 6 2016
10 comments
3,017 views