Skip to main content

Search

Welcome to Tibero GTS!

How to Check the Actual Capacity of UNDO Tablespace

Document Type | Technical Information

Category | Administration

Applicable Product Version | 7FS02PS

Document Number | TADTI049

 

Overview

When checking the DBA_DATA_FILES view, if the usage of the UNDO TABLESPACE is aggregated close to 100%, it can sometimes be difficult to determine whether expansion is actually necessary.
This is because UNDO segments do not get immediately released after use; even after being marked as EXPIRED, the principle is to reassign the segments that were previously EXPIRED upon reuse.
Therefore, to accurately understand the actual usage status of the UNDO TABLESPACE, you need to query through a different view.
 
 
 
 

Method

How to Check FREE SIZE

If the UNDO RETENTION period has sufficiently passed, FREE SIZE can be checked as follows.

select ts_name as "TABLESPACE_NAME",
ROUND(total_size/1024/1024,2) as "TOTAL_SIZE(MB)",
ROUND((total_size - free_size) / 1024/1024 ,2)as "USED_SIZE(MB)",
ROUND(free_size/1024/1024,2) as "FREE_SIZE(MB)",
ROUND(( free_size / total_size) * 100,2) as "FREE_SIZE(%)"
from v$undo_free_space;

 

  • V$UNDO_FREE_SPACE

    TABLESPACE_NAME TOTAL_SIZE(MB) USED_SIZE(MB) FREE_SIZE(MB) FREE_SIZE(%)
UNDO                      1024         44.12        979.88        95.69

 

  • DBA_DATA_FILES

    In this view, usage may be aggregated as 99.99, which can cause confusion.

    Tablespace Infomation
Tablespace Name         Bytes(MB)     Used(MB)  Percent(%)     Free(MB)  Free(%) MaxBytes(MB) MAX_Free(%)
UNDO                        1,024        1,024       99.99            0      .01        1,024         .01
SYSSUB                         26           25       95.67            1     4.33       32,768       99.92
SYSTEM                        116          104       89.44           12    10.56       32,768       99.68
USR                           292          171       58.39          122    41.61       32,768       99.48

 

 

 

 

 

Related to