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