Skip to content

Undo Usage

February 22, 2012

How UNDO space is Used?

When UNDO space is USED its divided in to 3 sections (EXPIRED,UNEXPIRED,ACTIVE).

SQL> select distinct status from dba_undo_extents;
 
STATUS
———
EXPIRED
UNEXPIRED
ACTIVE
 
The meanings of the values in the status column are as follows:
ACTIVE – There is an active transaction using this undo extent.
EXPIRED – The extent can be reused.
UNEXPIRED – There are no active transactions, but the extent is not available for reuse.
Unexpired Extents – Undo data whose age is less than the undo retention period.
 
While there are UNEXPIRED extents it won’t be possible to drop the UNDO1 tablespace. Once all extents are EXPIRED then we can dropt it.
 
To Check tablespace Usage
================================
 SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024/1024) “Tot size GB”,
 round(SUM(A.SUMB)/1024/1024) “Tot Free MB”,
 round(SUM(A.SUMB)*100/SUM(A.TOTS)) “%FREE”,
 100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) “%USED”,
 round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
 FROM (
 SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
 MAX(BYTES) LARGEST,COUNT(*) CHUNKS
 FROM SYS.DBA_FREE_SPACE A
 GROUP BY TABLESPACE_NAME
 UNION
 SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
 FROM SYS.DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
 where A.TABLESPACE_NAME=’&TBS’
 GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
 order by 5
/
old  15: where A.TABLESPACE_NAME=’&TBS’
new  15: where A.TABLESPACE_NAME=’UNDOTBS2′
 
TABLESPACE_NAME                Tot size GB Tot Free MB      %FREE      %USED   MAX_FREE CHUNKS_FREE

—————————— ———– ———– ———- ———- ———- ———–UNDOTBS2                                87       17228         19         81        944         485

From Above
===========
Total allocated is : 87 GB
Free : 17 GB
Used : 70 GB (81%)

So In 70 GB we have (ACTIVE, UNEXPIRED, EXPIRED). Since undo tablespace is re-usable, an Expired extent tells how many extents can be re-used.

But Actual Usage is as Below (ACTIVE + UNEXPIRED) & EXPIRED extents will be reused as an when necessary.

==============================================================================================================

select TBS,PCT_INUSE,USED_GB,EXPIRE “GB_FREE_TO_REUSE”,USED_GB+EXPIRE TOTAL_USED_GB
from (
select ‘&&tbs’ TBS,
((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name=’&tbs’
and status in (‘ACTIVE’,’UNEXPIRED’)) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name=’&tbs’) “PCT_INUSE” ,
(select sum(bytes)/1024/1024/1024
from dba_undo_extents
where tablespace_name=’&tbs’
and status in (‘ACTIVE’,’UNEXPIRED’)) “USED_GB”,
(select sum(bytes)/1024/1024/1024
from dba_undo_extents
where tablespace_name=’&tbs’
and status in (‘EXPIRED’)) “EXPIRE”
from dual
)
/
 
 
TBS       PCT_INUSE    USED_GB GB_FREE_TO_REUSE TOTAL_USED_GB
——– ———- ———- —————- ————-
UNDOTBS2   64.34641 56.2390137       14.5235596    70.7625732

14 GB (Expired extents from undo) + 17 GB (Free space from TBS) = 31 GB.

So Actually speaking Now you have 31 GB Free space From allocated 87GB of undotablespace.

 In Undo Segments there are three types of extents, they are
================================================================
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

The sequence for using extents is as follows,

  1. A new extent will be allocated from the undo tablespace when the requirement arises. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.
  2. If this fails because of no available free extents and we cannot autoextend the datafile,then Oracle tries to steal an expired extent from another undo segment.
  3. If it still fails because there are no extents with expired status then Oracle tries to reuse an unexpired extent from the current undo segment.
  4. If even that fails, Oracle tries to steal an unexpired extent from another undo segment.
  5. If all the above fails, an Out-Of-Space error will be reported.

 

References:

TROUBLESHOOTING GUIDE (TSG) – ORA-1555 (Doc ID 467872.1)

 

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: