Oracle has provided very useful tools and views to aid the DBA in database management> This includes a number of offerings dealing with the subject of space. One such view is DBA_TABLESPACE_USAGE_METRICS, which provides a ‘thumbnail’ view of overall size and used space. Unfortunately, this view doesn’t accurately report such usage for the UNDO tablespace in version 12.x, a result of the definition of the view that hasn’t changed for several versions. Oracle, between versions 11 and 12, decided to change how datafiles were identified in the FLAG column of GV$FILESPACE_USAGE. Let’s explore this issue a bit further.
In days of yore (before version 12) the UNDO tablespace had its datafiles flagged with a value of 6 in GV$FILESPACE_USAGE, and it was the only tablepsace identified in this manner. As such the view DBA_TABLESPACE_USAGE_METRICS relied on that flag to identify and subsequently report on UNDO usage and space. The view definition is shown below:
SELECT t.name,
tstat.kttetsused,
tstat.kttetsmsize,
(tstat.kttetsused / tstat.kttetsmsize) * 100
FROM sys.ts$ t, x$kttets tstat
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ = 0 and
bitand(t.flags, 16) <> 16 and
t.ts# = tstat.kttetstsn
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, v$filespace_usage f
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ <> 0 and
f.flag = 6 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
f.inst_id = param.inst_id and
param.name = 'undo_tablespace' and
t.name = param.value and
f.flag = 6 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#;
UNDO space is reported by the second query of the UNION. Notice that f.flag is looking only for a value of 6. In version 12.1 when the UNDO tablespace is created using ‘normal’ files (stored in a declared path, such as /u01/app/oracle/datafiles/…), the datafile is flagged as 6. When additional files are added, however, they are flagged as 14 according to MOS Bug 28821847. So, even though DBA_TABLESPACE_USAGE_METRICS reports the UNDO tablespace, it only reports information for the first datafile:
SQL> select * From dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
AUD_01 1664 1536000 .108333333
DATA_01 2021824 2560000 78.9775
DATA_02 744064 1280000 58.13
DATA_03 499712 1536000 32.5333333
DRSYS 116376 1536000 7.5765625
INDEX_01 476352 768000 62.025
INDEX_02 650752 768000 84.7333333
INDEX_03 516096 768000 67.2
LAW_01 512 128000 .4
LOB_01 2552 256000 .996875
LOB_02 3705856 3932160 94.2447917
OGGUSER_DATA 128 256000 .05
SYSAUX 1091392 2048000 53.290625
SYSTEM 292968 2816000 10.4036932
TEMP 0 3932160 0
TOOLS 344 512000 .0671875
UNDO_TS 928 1024000 .090625
USERS 1979448 2609152 75.8655686
USERS_1M 1024 1024000 .1
XDB 5976 256000 2.334375
20 rows selected.
SQL>
The actual UNDO tablespace size is far greater than reported by the view:
TABLESPACE_NAME TABLESPACE_SIZE
------------------------------ ---------------
UNDO_TS 1887436800
So, relying on the information in DBA_TABLESPACE_USAGE_METRICS for release 12.1.0.x is not recommended.
In version 12.2
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
----------------------------------- ---------- --------------- ------------
AUD_01 1024 262144 .390625
SYSAUX 208464 524288 39.7613525
TEMP 512 4194176 .012207404
TOOLS 128 12800 1
USERS 1256 4194176 .029946287
5 rows selected.
Querying dba_data_files and dba_free_space provides the missing information:
SQL> select f.tablespace_name, sum(f.user_bytes) used_space, sum(f.bytes) tablespace_size, sum(fs.bytes) free_space
2 from dba_data_files f, dba_free_space fs
3 where fs.tablespace_name = f.tablespace_name
4 and f.tablespace_name like 'UNDO%'
5 group by f.tablespace_name
6 /
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE FREE_SPACE
----------------------------------- -------------- --------------- --------------
UNDO_TS 24895291392 24914165760 1308360704
SQL>
This issue first appeared on My Oracle Support for version 12.2 where the UNDO tablespace was no longer reported by the view. Since the tablespace is reported in 12.1.0.x, the ‘missing space’ issue has not been reported to MOS, which is why it’s being reported here.
Oracle support reports that resizing datafiles in the UNDO tablespace will set the flag properly, but only if a declared path is used for the datafiles. If the database is configured for Oracle Managed Files the situation changes and, after resizing the datafile, the flag is set to 10:
SQL> SELECT f.flag
2 FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
3 WHERE
4 t.online$ != 3 and
5 t.bitmapped <> 0 and
6 f.inst_id = param.inst_id and
7 param.name = 'undo_tablespace' and
8 t.name = param.value and
9 t.ts# = f.tablespace_id;
FLAG
----------
10
SQL>
So, the UNDO tablespace still does not appear in the output from DBA_TABLESPACE_USAGE_METRICS.
Given all of the above information, it’s better to query DBA_DATA_FILES and DBA_FREE_SPACE to monitor UNDO usage as DBA_TABLESPACE_USAGE_METRICS cannot provide reliable data in 12.1.0.x, and it provides absolutely no data in 12.2.0.x. It is possible that Oracle will change the view definition to reflect the actual flag settings for the UNDO tablespace files, but for now, it’s best to query the views (DBA_DATA_FILES, DBA_FREE_SPACE) known to contain relevant and accurate data.
