UNDO segments not Releasing Space

Share via:

Dear Readers,

In this article, we will see UNDO segments not Releasing Space .

UNDERSTANDING:

There are three status of UNDO segments:

  • ACTIVE: Currently in Used
  • EXPIRED: Extend cannot be Used
  • UNEXPIRED: unexpired extents were reused

The UNDO records are not deleted when they are expired. They stay and are overwritten only when a new UNDO record needs to be written and there is no free space. Thus, it is normal for UNDOTBS1 to appear at 99% full.
When there are no EXPIRED extents left for reuse and it can not allocate more free extents and AUTOEXTEND is not in use, the “unable to extend segment by 8 in undo tablespace ‘UNDOTBS1′” will occur. Only In this case does a new datafile need to be added to UNDOTBS1.
INVESTIGATION:

Get the status of UNOD segments

Example:
STATUS    NUM_EXTENTS
————————————–
ACTIVE    9 <–There were free extents to use
EXPIRED   6 <–1 extent was reused
UNEXPIRED 40 <–1 extent already expired

Solution:
if You can see if the undo tablespace has retention Guarantee enabled with the following query:
select retention from dba_tablespaces where tablespace_name='<tbs_name>’;
RETENTION
—————–
NOGUARANTEE

Example 1: Autoextend off in UNDO DATAFILE .
when DML operation happening then Why Expired extents were not reused?
For example :    performing updates in batches of 10,000 so perhaps the extents that already exists are too small to allocate the undo data that my update generated. Remember that the Undo tablespace extents are auto allocated, so the first extents are small, (64KB at the beginning).

STATUS    NUM_EXTENTS
——— —————————–
ACTIVE    46
EXPIRED   6
UNEXPIRED 12 <–unexpired extents were reused

If we get below error:
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’
This is because both Expired and Unexpired extents were not big enough to allocate my undo data and since the Tablespace has auto extend disabled Oracle raised an error saying that no more free space was found. As you can see Oracle did not resize any datafile:

TABLESPACE_NAME FILE_NAME                                   AUT MB
————— —————————————–  —  —————————-
UNDOTBS1        xxxxxxxxxx6                                 YES  5

Example 2: Autoextend=on

TABLESPACE_NAME FILE_NAME                                  AUT  MB
————— —————————————— —- —————————
UNDOTBS1        +DATA/orcl/datafile/undotbs1.264.882411811 YES  7
Now see Active  Extends  will get increasing

STATUS    NUM_EXTENTS
——— —————————-
ACTIVE    48 <–Datafile was autoextended and more free extents are available
EXPIRED   8
UNEXPIRED 28

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website               www.ktexperts.com
Facebook Page KTexperts
Linkedin Page   : KT EXPERTS

Follow Me On
Linkedin :Rakesh Monga

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment