UNDO segments not Releasing Space

Share via:

Post Views: 0

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:

select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

1	select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

Get the status of UNOD segments

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

1	select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

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).

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

1	select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

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:

select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

1	select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

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

Example 2: Autoextend=on

SQL> alter database datafile '+DATA/orcl/datafile/undotbs1.264.882411811' autoextend on next 1M maxsize 1G;
Database altered.

1 2	SQL> alter database datafile '+DATA/orcl/datafile/undotbs1.264.882411811' autoextend on next 1M maxsize 1G; Database altered.

SQL> select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

1	SQL> select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';

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

1* select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

1	1* select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status

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

select begin_time, UNXPSTEALCNT "#UnexpiredBlksTaken",
EXPSTEALCNT "#ExpiredBlksTaken", NOSPACEERRCNT "SpaceRequests"
from v$undostat order by begin_time;

select begin_time, UNXPSTEALCNT "#UnexpiredBlksTaken",

EXPSTEALCNT "#ExpiredBlksTaken", NOSPACEERRCNT "SpaceRequests"

from v$undostat order by begin_time;

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