Oracle Database Discussions

Hi Experts,

I have a general query regarding the status of the undo extents. The undo retention value of my database is default value of 1800.

I have run the following query to see how the space is used in the undo extents.

SQL> select count(segment_name),sum(bytes/1024/1024),status from dba_undo_extents group by status
2 /

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS
------------------- -------------------- ---------
1 8 ACTIVE
1116 3599.67578 EXPIRED
601 153.375 UNEXPIRED


Now as you can see most of the extents in the undo tablespace are expired. Now could you please correct me as i am assuming that all the expired extents would be used by any new transaction if it needs space where as the unexpired extents are those that are still in the undo retention value of 900 after which they too become expired?

Do i need to extend the max value in this case of can i just leave it just like that since the expired extents will be re-used.

NAME TYPE VALUE
------------------------------------ ----------- ----------------
undo_management string AUTO
undo_retention integer 1800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDO


I would be really greatful if you could clear up on these extent status

thanks in advance