Trouble with Oracle’s DBA_TABLESPACE_USAGE_METRICS For The UNDO Tablespace

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:

        (tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM  sys.ts$ t, x$kttets tstat
  WHERE$ != 3 and
        t.bitmapped <> 0 and
        t.contents$ = 0 and
        bitand(t.flags, 16) <> 16 and
        t.ts# = tstat.kttetstsn
 SELECT, sum(f.allocated_space), sum(f.file_maxsize),
     FROM sys.ts$ t, v$filespace_usage f
     WHERE$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ <> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY, f.tablespace_id, t.ts#
 SELECT, sum(f.allocated_space), sum(f.file_maxsize),
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE$ != 3 and
     t.bitmapped <> 0 and
     f.inst_id = param.inst_id and = 'undo_tablespace' and = param.value and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY, 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;

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


The actual UNDO tablespace size is far greater than reported by the view:

------------------------------ ---------------
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 none of the UNDO tablespace files are flagged as 6 (even when using a declared path) and the tablespace ‘disappears’ from the view entirely as these results from a newly created 12.2 database show:

----------------------------------- ---------- --------------- ------------
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  /

----------------------------------- -------------- --------------- --------------
UNDO_TS 			       24895291392     24914165760     1308360704


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$ != 3 and
  5     t.bitmapped <> 0 and
  6     f.inst_id = param.inst_id and
  7 = 'undo_tablespace' and
  8 = param.value and
  9     t.ts# = f.tablespace_id;



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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles